On Apr 27, 9:24 am, Zuzeppeddu wrote:
Hi

I have twenty worksheets in a workbook. Each worksheet has nine
tables. All the tables are identical in sturcture, but have different
data. I want to create new tables using these tables. Since there are
another forty to fifty files to be done, I want to automate this
process.

The tables look like this:

In sheet1:

Apr-05 0 0 15 5 0 * *
May-05 0 0 14 9 5 7 *
Jun-05 0 * 19 9 * 13 0
Jul-05 0 * 15 13 * 9 *
Aug-05 0 0 17 11 * * *
Sep-05 2 0 14 9 * 10 *
Oct-05 0 0 20 13 * 9 *
Nov-05 0 * 19 9 * * *
Dec-05 0 0 13 7 * 7 *
Jan-06 0 * 14 10 * 6 *
Feb-06 0 0 12 5 5 7 *
Mar-06 0 0 10 9 * 5 *

Apr-05 0 0 0
May-05 0 * *
Jun-05 0 * *
Jul-05 0 * *
Aug-05 * 0 *
Sep-05 0 * *
Oct-05 0 * *
Nov-05 0 * *
Dec-05 0 0 0
Jan-06 0 0 0
Feb-06 0 * *
Mar-06 0 * *

...another seven tables in sheet1...

All tables have two row spaces between them.

Another 19 sheets with exactly the same number of tables.

In a new worksheet, I would like to create a new table like so:

sheet1-table1-Row1
sheet2-table1-Row1
sheet3-table1-Row1
sheet4-table1-Row1
sheet5-table1-Row1
sheet6-table1-Row1
sheet7-table1-Row1
sheet8-table1-Row1
sheet9-table1-Row1
sheet10-table1-Row1
sheet11-table1-Row1
sheet12-table1-Row1
sheet13-table1-Row1
sheet14-table1-Row1
sheet15-table1-Row1
sheet16-table1-Row1
sheet17-table1-Row1
sheet18-table1-Row1
sheet19-table1-Row1
sheet20-table1-Row1

Then create another table:

sheet1-table2-Row1
sheet2-table2-Row1
sheet3-table2-Row1
sheet4-table2-Row1
sheet5-table2-Row1

...etc.

I would be very grateful for any pointers or code examples.

Thanks

You'll probably need to use a nested loop. An outside for loop can
loop through the worksheets and the nested for loop (or do loop) can
loop through the data on an individual worksheet. You can create an
output worksheet to dump the worksheet table data into. Here are some
ideas of what you might need (you can search these in VBE for more
detail and example code): UsedRange, CurrentRegion, For...Next Loop,
For Each Loop, Do Loop, Offset, Copy, and PasteSpecial.

Matt