Paste, format, Insert new Tables

[rana101]

Hello All,

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.

[Frosty]

After your paste and before you format, simply insert a paragraph as well, so that your 3 tables have a "break" between them.
Selection.InsertAfter VbCr
Should work for you.

[Frosty]

At least, that should separate into 3 tables. I'm not sure if your data will be different in the three tables. I have yet to look at your attachment.

[rana101]

Hello Frosty,
Thank you very much for your kind reply.
I have tried your solution without luck.
The result was same as before.
Even the formattiong stuff is not working correcly.

Please suggest me some more ideas.

Thanks and Kind Regards,
Naidu

[macropod]

Cross-posted at: http://www.techsupportforum.com/forums/f57/paste-format-insert-new-tables-612457.html

For cross-posting etiquette, please read: http://www.excelguru.ca/node/7 (wlmailhtml:{64CE8C15-0C83-4021-B475-A9AF6CEEA4DC}mid://00000064/!x-usc:http://www.excelguru.ca/node/7)

[Alpensopath]

Click the Microsoft Office Button and then click Word Options.
Click Popular.
Under Top options for working with Word, select the Show Developer tab in the Ribbon check box.
On the Developer tab, in the Code group, click Record Macro.
Do one of the following:
Begin recording To begin recording the macro without assigning it to a button on the Quick Access Toolbar or to a shortcut key, click OK.
Create a button To assign the macro to a button on the Quick Access Toolbar, do the following:
Click Button.
Under Customize Quick Access Toolbar, select the document (or all documents) for which you want to add the macro to the Quick Access Toolbar.
To make your macro available in all documents, be sure to clickNormal.dotm.
Under Choose commands from dialog box, click the macro that you are recording, and then click Add.
To customize the button, click Modify.
Under Symbol, click the symbol that you want to use for your button.
In the Display name box, type the macro name that you want to display.
Click OK twice to begin recording the macro.
The symbol that you choose is displayed in the Quick Access Toolbar. The name that you type is displayed when you point to the symbol.
Assign a keyboard shortcut To assign the macro to a keyboard shortcut, do the following:
Click Keyboard.
In the Commands box, click the macro that you are recording.
In the Press new shortcut key box, type the key sequence that you want, and then click Assign.
Click Close to begin recording the macro.
Perform the actions that you want to include in the macro.
When you record a macro, you can use the mouse to click commands and options, but not to select text. You must use the keyboard to select text. For more information about selecting text by using the keyboard, see Select text.
To stop recording your actions, click Stop Recording in the Code group.
Click the Microsoft Office Button and then click Word Options.
Click Customize.
Next to Keyboard shortcuts, click Customize.
In the Categories list, click Macros.
In the Macros list, click the macro that you want to change.
In the Press new shortcut key box, type the key combination that you want to choose.
Check the Current keys box to make sure that you aren't assigning a key combination that you already use to perform a different task.
In the Save changes in list, click the option that matches where you want to run your macro.
To make your macro available in all documents, be sure to clickNormal.dotm.
Click Close.
On the Developer tab, in the Code group, click Macros.
In the list under Macro name, click the macro that you want to run.
Click Run.
Write a macro from scratch
On the Developer tab, in the Code group, click Macros.
In the Macro name box, type a name for the macro.
If you give a new macro the same name as a built-in macro in Office Word 2007, the new macro actions will replace the built-in macro. To view a list of built-in macros, click Word Commands in the Macros in list.
In the Macros in list, click the template or document in which you want to store the macro.
To make your macro available in all documents, be sure to click Normal.dotm.
Click Create to open the Visual Basic Editor.
After you open the Visual Basic Editor, you may want more information about working with Visual Basic for Applications. For more information, click Microsoft Visual Basic Help on the Help menu or press F1.