creating a table from twenty worksheets

[Zuzeppeddu]

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

 

[matt]

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

 

[Zuzeppeddu]

Hi

I sent a reply yesterday but it didn't appear in the thread. So here
it is again:

I have slightly simplified my requirements. Here is the scenario
again:

From sheet1 to sheet20, there is a table in range A1930:

Apr-05 * * *
May-05 11 * 7
Jun-05 * 6 *
Jul-05 * * *
Aug-05 * * *
Sep-05 * * *
Oct-05 6 * 8
Nov-05 * 9 *
Dec-05 * * *
Jan-06 9 * *
Feb-06 * * *
Mar-06 * * *

In sheet21 I want to create 20 tables. All the Apr-05 rows will create
the first table, May-05 rows will create the second table, so on and
so forth.

The code I have come up with is:

Sub myTables()
Dim RowNdx1 As Integer
Dim RowNdx2 As Integer
Dim RowNdx3 As Integer

For RowNdx1 = 1 To 20 Step 1
For RowNdx2 = 17 To 30 Step 1
For RowNdx3 = 1 To 240 Step 1
Sheets("sheet" & RowNdx1).Select
Rows(RowNdx2 & ":" & RowNdx2).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("sheet21").Select
Rows(RowNdx3 & ":" & RowNdx3).Select
ActiveSheet.Paste
Next RowNdx3
Next RowNdx2
Next RowNdx1

End Sub

This code runs and goes into an infinite loop. I am sure I have not
coded it correctly, but don't know where the mistake is.

Any help will be very appreciated.

Thanks

 

[cbhartness]

The sheet.select steps are really slow. It might not be an infinite
loop. To speed up loops as written, try
application.screenupdating=false before your loops, and
application.screenupdating=true after.

A better approach with this many copies is to do the copy/paste in a
single step, such as
Sheets("sheet" & RowNdx1).Rows(RowNdx2 & ":" & RowNdx2).Copy _
Destination:=Sheets("sheet21").Rows(RowNdx3 & ":" & RowNdx3)
With this approach, you won't need the application.screenupdating step
because sheets are not selected.

Carl.