Simplifying code from excel to word

[Staal]

Hi,

I have been struggling finding an easier way to write the following code:

With xlBook.Worksheets("Sheet1")
Range("A1").Select
For i = 1 To 8
ActiveCell.Value = i
wdTable.Cell(i, 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i, 2).Range.Text = .Cells(2, i).Text
wdTable.Cell(i + 9, 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + 9, 2).Range.Text = .Cells(2 + 1, i).Text
wdTable.Cell(i + (2 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (2 * 9), 2).Range.Text = .Cells(2 + 2, i).Text
wdTable.Cell(i + (3 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (3 * 9), 2).Range.Text = .Cells(2 + 3, i).Text
wdTable.Cell(i + (4 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (4 * 9), 2).Range.Text = .Cells(2 + 4, i).Text
wdTable.Cell(i + (5 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (5 * 9), 2).Range.Text = .Cells(2 + 5, i).Text
wdTable.Cell(i + (6 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (6 * 9), 2).Range.Text = .Cells(2 + 6, i).Text
wdTable.Cell(i + (7 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (7 * 9), 2).Range.Text = .Cells(2 + 7, i).Text
wdTable.Cell(i + (8 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (8 * 9), 2).Range.Text = .Cells(2 + 8, i).Text
wdTable.Cell(i + (9 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (9 * 9), 2).Range.Text = .Cells(2 + 9, i).Text
wdTable.Cell(i + (10 * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (10 * 9), 2).Range.Text = .Cells(2 + 10, i).Text

Any help will be highly appreciated,

Best regards,

Soren

[Bob Phillips]

With xlBook.Worksheets("Sheet1")

Range("A1").Select
For i = 1 To 8

ActiveCell.Value = i
wdTable.Cell(i, 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i, 2).Range.Text = .Cells(2, i).Text

For j = 1 To 10

wdTable.Cell(i + (j * 9), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (j * 9), 2).Range.Text = .Cells(2 + j, i).Text
Next j

[Staal]

Thank you very much for your quick response.

I do have one other problem which I hope you can answer. Instead of 9 i would like to make the number dynamic - i.e. dependent on the number of cells in the first row (A1:M1) where there is text (similar to the count function in excel). Any help would be highly appreciated.

Best regards,

Soren

[Bob Phillips]

Multiplier = Application.CountA(Range("A1:M1")

With xlBook.Worksheets("Sheet1")

Range("A1").Select
For i = 1 To 8

ActiveCell.Value = i
wdTable.Cell(i, 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i, 2).Range.Text = .Cells(2, i).Text

For j = 1 To 10

wdTable.Cell(i + (j * Multiplier), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (j * Multiplier), 2).Range.Text = .Cells(2 + j, i).Text
Next j