I have an excel sheet which I want to convert into a new word document with multiple tables.

Basically i want each excel row as a new word table.

The new table should have 2 columns.The excel row should now be converted into the second word column and the first column should have the corresponding header.

Say for example, take the below data as in the excel sheet.


ID NAME
01 A
02 B

I want to convert the above data in each row as a separate word table. Here, ID and NAME are the headers..and they should be repeated as a column for each table as shown below

ID 01
NAME A



ID 02
NAME B


and so on.... The word tables should be automatically generated depending on the no of rows in the excel sheet.

This is the code I got up till now

Sub CreateNewWordDoc()
' add a reference to the Word-library
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdTable As Word.Table
Dim xText, xText2
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
Set wrdRange = wrdDoc.Range
wrdApp.Visible = True

For i = 1 To 10
xText = Worksheets("Sheet1").Cells(i, 1).Value
xText2 = Worksheets("Sheet1").Cells(i, 2).Value

Set wrdRange = wrdDoc.Range
With wrdRange
.Collapse Direction:=wdCollapseEnd
.InsertParagraphAfter
.Collapse Direction:=wdCollapseEnd
End With

'Create table
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)

With wrdTable
With .Cell(i, 1).Range
.InsertAfter xText
End With
xText = Worksheets("Sheet1").[a2].Value
With .Cell(i, 2).Range
.InsertAfter xText2
End With
End With
Next i
'------new code-----------

'clean up
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub


But this does not work for the repeated headers and conversion of the row into column. I can't figure out how to do it. Please help me with the same.