asked on

Copy name range from Excel to Word bookmarks using VBA

I am trying to figure out how to use VBA to copy formatted text from a named range from Excel to a closed Word file.

The code I have thus far is below.

In sub CopyRangetoWord I am trying to copy the contents the named range "NameRange1" from Excel sheet "Stats" to word document "C:\Users\Me\Desktop\MacroTest.docx" at bookmark "grid1test".

I believe the problem lies here: " .Bookmarks("grid1test").Range.Text = ws.Range(NamedRange1).Value", but am not certain how to address. Please assist.

Code below

'Returns "Run-time error '1004': Method 'Range' of object '_Worksheet' failed
'It does successfuly open the word document
Sub CopyRangetoWord()
Dim objword As Object
Dim ws As Worksheet
 Set ws = ActiveWorkbook.Sheets("Stats")
 Set objword = CreateObject("Word.Application")
 objword.Visible = True
 objword.Documents.Open "C:\Users\Me\Desktop\MacroTest.docx"
 With objword.ActiveDocument
    .Bookmarks("grid1test").Range.Text = ws.Range(NamedRange1).Value ' Need to copy range
End With
End Sub

Open in new window

Subodh Tiwari (Neeraj)

Try this.....

.Bookmarks("grid1test").Range.Text = ws.Range("NamedRange1").Value

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aarick161

ASKER

Subodh,

I applied your solution and am receiving an error: Run-rime error '13': Type mismatch

Roy,

How would I apply your code to specific range and specific bookmark?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

If you only want to copy certain ranges then you would need to specify them in the code.

Frank Helk

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.