Hi, I am Macro beginner and I am trying to write a macro in Excel 2013 that will copy data from a single sheet and paste it into a word 2013 doc. The data in the excel sheet consists of various tables with varying columns and rows. The sample data is as follows:
Sample.xlsx
I would like the code to go through the excel sheet and identify the various tables and copy the tables into a word doc. The tables should be copied one after the other with some spacing between them and should have proper formatting such as auto fit column width.
I tried writing the code but all I have been able to do is to copy data from excel and paste it as a whole to the doc. My code is shown below.
Sub MacroStudent()
'Step 1: Declare your variables
Dim MyRange As Excel.Range
Dim MyRange1 As Excel.Range
Dim MyCell As Excel.Range
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim WdRange As Word.Range
Dim wdTable As Word.Table
Dim wdBreak As Word.Break
Dim LastRow As Long
Dim LastColumn As Long
'Step 1.1: Capture the last used row and column number.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Step 2: Copy the defined range
Sheets("Page1-1").Range("A9:j14").Copy
'Step 3: Open the target Word document
Set wd = New Word.Application
Set wdDoc = wd.Documents.Add 'create a new document
wd.Visible = True
'Step 4: Set focus on the target
Set WdRange = wdDoc.Range
'Step 4.1: Create a blank table in Word
Set wdTable = wdDoc.Tables.Add(Range:=WdRange, NumRows:=62, NumColumns:=20)
'Step 5: Delete the old table and paste new
On Error Resume Next
WdRange.Tables(1).Delete
WdRange.Paste 'paste in the table
'Step 6: Adjust column widths
WdRange.Tables(1).AutoFitBehavior wdAutoFitWindow
'WdRange.Tables(1).Columns.AutoFit
'Step 7: Memory cleanup
Set wd = Nothing
Set wdDoc = Nothing
Set WdRange = Nothing
End Sub
Can anyone help me with this? Thanks in advance.