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:
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:
- Place your cursor to the location you want to be remembered
- Navigate to the Insert tab in the Ribbon
- Click the Bookmark button in the Links group
- Give your bookmark a name (no spaces)
- 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.
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.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
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.