×

Copy & Paste Multiple Excel Tables Into Microsoft Word With VBA

By Chris Newman •  Updated: 10/06/14 •  6 min read
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 upfront and will expand on some key changes below the code.

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

Copy & Paste Multiple Excel Tables Into Microsoft Word With VBA

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)
'SOURCE: www.TheSpreadsheetGuru.com

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
      tbl.Copy
    
    'Paste Table into MS Word (using inserted Bookmarks -> ctrl+shift+F5)
      myDoc.Bookmarks(BookmarkArray(x)).Range.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=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
  
'ERROR HANDLER
WordDocNotFound:
  MsgBox "Microsoft Word file 'Excel Table Word Report.docx' is not currently open, aborting.", 16

'Put Stuff Back The Way It Was Found
EndRoutine:
'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 to be 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
How To Add A Bookmark In Microsoft Word Document

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.

  ActiveDocument.Bookmarks("MyBookmarkName").Select

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 set up in a way that I could easily loop through the worksheet positions.  If your worksheet positions do not correspond with your tables (for example you may want to loop through Sheet1, Sheet3, and 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.  

Download Example Excel File

If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.


Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.