I am a newbie to Word VBA macros Programming.
I have a probelem in copying data from Excel sheets and placing them in new tables in a word document. The requirement is as follows:
I need to program a macro that opens an Excel document stored at a particular location. Go to a sheet in the file. Copy a specific range of data inside the sheet, Place the data in a new table inside word document, the table should be autoformattted to type Word table colourful2 and contents of the table shall be autofit to window.
Following is the macro
Sub Macro1()
'
' Macro1 Macro
Dim xlApp As Object
Dim St As String
Dim i As Long
Dim ret As Long
Dim count As Integer
Dim myRg As Range
Dim myTbl As Table
' Open the Excel sheet saved in a particular location
' For example I have stored the table in D drive, you need to add the path of exact file location and also type
Set xlApp = GetObject("D:test.xlsx")
' Loop Over the number of tables you want to insert
' Here I have taken a count of 3 as test
For count = 1 To 3
' Go to the excel sheet
With xlApp.Sheets("CmdConfigurate")
' Loop over the specified range where you want to copy inside the sheet
' In every sheet I have the word Size as end row
For i = 5 To 100
If xlApp.Sheets("CmdConfigurate").Cells(i, 1).Value = "Size" Then ret = i
Next i
End With
' Copy the specified range
xlApp.Sheets("CmdConfigurate").Range("A5:G" & ret).Copy
Selection.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=True, _
RTF:=True
With ActiveDocument.Tables(ActiveDocument.Tables.count)
.Columns.AutoFit
.AutoFormat Format:=wdTableFormatColorful2
.AutoFitBehavior wdAutoFitContent
End With
Next count
Set xlApp = Nothing
End Sub
When I run this macro I am getting all the data in one table, what I want is to place the data in three seperate tables, please correct the macro.
In the attachment I an sending the excel file
Thanks and Kind Regards,
Naidu.