-1

I am working on Macro that copies data from NamedCells in Excel and places them at specific Bookmarks in a Word template. I am getting a run time error 9 when the code gets to the pasting data at Bookmarks line. Further, some of the data is getting pasted into the template but "Title1" is getting pasted at BookmarkTitle2 location and Title2 is getting pasted at BookmarkTitle3... then the run time error comes up...

Code is below....

Can someone tell me what I am doing wrong?

Sub CopyExcelTitlesToWord()

    Dim WordApp As Word.Application
    Dim myDoc As Word.Document
    Dim WordTable As Word.Table
    Dim BookmarkArray As Variant
    Dim Title(1 To 3) As Range
    Dim x As Integer

'List the tables/charts from excel you want to Word
     Set Title(1) = ThisWorkbook.Worksheets("TopPage").Range("Title1")
     Set Title(2) = ThisWorkbook.Worksheets("TopPage").Range("Title2")
     Set Title(3) = ThisWorkbook.Worksheets("TopPage").Range("Title3")


'List of corresponding Word Bookmarks to paste the tables/charts to in Word
     BookmarkArray = Array("BookmarkTitle1", "BookmarkTitle2", "BookmarkTitle3")

'Optimize Code
    Application.ScreenUpdating = False
    Application.EnableEvents = False

'Open Word template
     Set WordApp = CreateObject("Word.Application")
     WordApp.Visible = True

'Open existing template in Word
    Set myDoc = WordApp.Documents.Open("C:\Users\xxx\Desktop\TemplateTest1.docx")

'Loop Through and Copy/Paste Multiple Excel NamedCells
    For x = LBound(Title) To UBound(Title)
    Title(x).Select
    Selection.Copy
     'Paste Title into MS Word (using inserted Bookmarks -> ctrl+shift+F5). 'Name the Bookmarks so they are in Series so they are easy to loop through.
    myDoc.Bookmarks(BookmarkArray(x)).Range.PasteExcelTable False, False, True
    Next x
         'Optimize Code
    Application.ScreenUpdating = True
    Application.EnableEvents = True

'Clear The Clipboard
    Application.CutCopyMode = False

End Sub
Deduplicator
  • 44,692
  • 7
  • 66
  • 118
user6447455
  • 1
  • fwiw, I've found that using field codes on user defined vars in word works when the [excel-vba] crosses over to word and redefines those specific field code vars. It's like a home-built mail merge. If you have trouble getting that working I will look up the code to write to a word template. –  Jun 29 '16 at 06:15

1 Answers1

-1

BookmarkArray will be a 0 based array (0..2) - see Excel help on the Array function.

I suggest you change

Dim Title(1 To 3) As Range

to

Dim Title(0 To 2) As Range

and change the hard coded indexes accordingly.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • 1
    Also, don't use `Select`. [See this Q for help on this](http://stackoverflow.com/q/10714251/445425) – chris neilsen Jun 29 '16 at 06:42
  • Thanks everyone for the feedback... Chris with you solution I got the code to work... Cheers – user6447455 Jul 03 '16 at 01:59
  • The solution above is working great. I have one more question that is related. I have a very similar sub that I want to run after the one with the titles, to paste the body of the tables into the same word template, which has already been open by the sub above... If I just use myDoc = ActiveDocument it works fine if I run the sub separately after the one above... BUT as soon as I link the two subs together I get a run time error '462'... how do I solve for this? – user6447455 Jul 11 '16 at 12:06