I’m working on a VBA script to copy a single cell from Excel and paste it into a Word document at a specific bookmark. I want to paste not just the text but also preserve the formatting of the cell. I’m using .PasteExcelTable with the parameters False, False, True to achieve this.

However, I’m encountering an issue where the pasted content doesn’t replace the existing bookmark text. Instead, it’s added before the text, and if I run the code again, the new text is inserted before the old one, which is not the behavior I desire.

Here is the function I’m using:

Function ReplaceContentAtBookmark(TWD As Word.Document, ByVal BookmarkName As String)
    
    Dim bmRange As Word.Range
    Set bmRange = TWD.Bookmarks(BookmarkName).Range
    
    ' Delete the previous element
    bmRange.Delete

    bmRange.PasteExcelTable False, False, True
    
    ' Recreate the bookmark with the new range
    TWD.Bookmarks.Add Name:=BookmarkName, Range:=bmRange

End Function

This is the single cell to copy from Excel:

This is the output wanted: The text in the Word document bookmark should be replaced with the new text from the Excel cell, retaining the cell’s formatting.

This is the output that I get: The new text is inserted before the existing text (if I run multiple times the code), and the bookmark range doesn’t seem to update to include the new text.