VBA Excel Picture to Word Bookmark macro

Question

I am trying to amend a VBA macro to enable pasting of an Excel range (as a picture, for formatting purposes) to a Word bookmark.

Sub test2()
Dim objWord As Object
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("PREMIUMS")
Set objWord = CreateObject("Word.Application")

objWord.Visible = True
objWord.Documents.Open "C:\TEST\BTM Macro Template.docx"

 With objWord.ActiveDocument
.Bookmarks("PLAN_1_SHEET").Range.Text = ws.Range("A34").Value
.Bookmarks("PLAN_2_SHEET").Range.Text = ws.Range("BTM_PREM").Value
End With
Set objWord = Nothing
End Sub

The macro pastes a single cell text reference fine ("A34"), but using the same code for a range "BTM_PREM") returns a type mismatch error. I know it is due to the range not being a string, but can't seem to identify how to amend this line to enable pasting of "BTM_PREM", as a picture, at the "PLAN_2_SHEET" bookmark.

.Bookmarks("PLAN_2_SHEET").Range.Text = ws.Range("BTM_PREM").Value

Answer 1

you could use Copy() method on Excel Range object and then either Paste() or PasteSpecial() or PasteExcelTable() Word Range object methods, like follows:

ws.Range("BTM_PREM").Copy
.Bookmarks("PLAN_2_SHEET").Range.Paste

or

ws.Range("BTM_PREM").Copy
.Bookmarks("PLAN_2_SHEET").Range.PasteSpecial Link:=True

or

ws.Range("BTM_PREM").Copy
.Bookmarks("PLAN_2_SHEET").Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=True

Answer 2

This is a piece of code that works for me:

ActiveWorkbook.Sheets("Lease 1").Range("B16:AF25").CopyPicture Appearance:=xlScreen, Format:=xlPicture
wdoc.Bookmarks("Bkmrk1").Range.Paste
Application.CutCopyMode = False 

It's not a complete macro, just a part of it so you'll have to adjust a bit, But I think you get the idea.