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.