Copy & Paste Multiple Excel Tables Into Microsoft Word With VBA

Copy & Paste Multiple Excel Tables Into Microsoft Word With VBA

Moving Excel Tables Into Various Word Pages 

This post is an add-on to a previous post I wrote covering how to copy & paste a single Excel table into a Word document.  I received a bunch of questions asking how to modify the code to handle copying multiple Excel tables to a Word document.  While I typically don't write entire posts to cover every possible scenario out there, I felt that this task would be a very good way to show how to take VBA code meant for singular use and turn it into an automating machine!  For those who are well versed in VBA I'll provide the entire VBA macro to you up front and will expand on some key changes below the code.

Here's a brief look at what the VBA code does:

VBA Code Looping Through Multiple Excel Tables

Option Base 1 'Force arrays to start at 1 instead of 0

Sub ExcelTablesToWord()

'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
  (VBE > Tools > References > Microsoft Word 12.0 Object Library)


Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table
Dim TableArray As Variant
Dim BookmarkArray As Variant

'List of Table Names (To Copy)
  TableArray = Array("Table1", "Table2", "Table3", "Table4", "Table5")
'List of Word Document Bookmarks (To Paste To)
  BookmarkArray = Array("Bookmark1", "Bookmark2", "Bookmark3", "Bookmark4", "Bookmark5")

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

'Set Variable Equal To Destination Word Document
  On Error GoTo WordDocNotFound
    Set WordApp = GetObject(class:="Word.Application")
    WordApp.Visible = True
    Set myDoc = WordApp.Documents("Excel Table Word Report.docx")
  On Error GoTo 0
'Loop Through and Copy/Paste Multiple Excel Tables
  For x = LBound(TableArray) To UBound(TableArray)

    'Copy Table Range from Excel
      Set tbl = ThisWorkbook.Worksheets(x).ListObjects(TableArray(x)).Range
    'Paste Table into MS Word (using inserted Bookmarks -> ctrl+shift+F5)
      myDoc.Bookmarks(BookmarkArray(x)).Range.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
    'Autofit Table so it fits inside Word Document
      Set WordTable = myDoc.Tables(x)
      WordTable.AutoFitBehavior (wdAutoFitWindow)

  Next x

'Completion Message
  MsgBox "Copy/Pasting Complete!", vbInformation
  GoTo EndRoutine
  MsgBox "Microsoft Word file 'Excel Table Word Report.docx' is not currently open, aborting.", 16

'Put Stuff Back The Way It Was Found
'Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True

'Clear The Clipboard
  Application.CutCopyMode = False

End Sub

Bookmarking Specific Locations In A Word Document

When you are trying to paste anything into a Word Document the best way to do so is to have a Document already created that has pinpointed areas mapped out.  You can do this by adding these little things called Bookmarks to your Word Document.  By doing this you can use VBA code to access these Bookmark locations and paste your objects to an exact place inside your document.  You can tie a bookmark either to a cursor location or a textual location.  In this example, I used a cursor location (2 lines below the table title).  

Bookmarks are very easy to add.  Here are the steps:

  1. Place your cursor to the location you want remembered
  2. Navigate to the Insert tab in the Ribbon
  3. Click the Bookmark button in the Links group
  4. Give your bookmark a name (no spaces)
  5. Click the Add button in the dialog box

Using VBA To Reference A Word Bookmark

It is very simple to reference a Bookmark with VBA.  It is very similar to how you would reference a named range in Excel.  Below is the proper syntax for a Bookmark reference.


List Out The Stuff You Want To Change

A very easy way to loop through multiple ranges, tables, graphs or any other Excel object is to list out the names you want to cycle through.  In this code, the Excel Table names were added to an array (aka list).  The Word Bookmark names were also added to a separate array list.  Notice that each position of table names in the list corresponds with the bookmark name we want it pasted to.  This way everything lines up while the code is looping.  

I did not create a separate array list for the worksheet names as they were setup in a way that I could easily loop through the worksheet positions.  If you your worksheet positions do not correspond with your tables (for example you may want to loop through Sheet1, Sheet3, Sheet7), then you would want to create an array with the worksheet names and reference those names in a similar fashion to the way the table and bookmark names were referenced.

'List of Table Names (To Copy)
  TableArray = Array("Table1", "Table2", "Table3", "Table4", "Table5")
'List of Word Document Bookmarks (To Paste To)
  BookmarkArray = Array("Bookmark1", "Bookmark2", "Bookmark3", "Bookmark4", "Bookmark5")

Create Your Automating Machine

Hopefully through this code you are able to create a super fast way to export your Excel data to a Word document (instead of using the manual copy/paste method).  If you have any questions about this post please let me know in the comments section below.  I try my best to reply back to everyone's' comments as they come in.  

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section


How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)