Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a table from twenty worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a table from twenty worksheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a table from twenty worksheets
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 A19:D30: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a table from twenty worksheets
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. On May 1, 3:54 am, Zuzeppeddu wrote: 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 A19:D30: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
120= rupees one hundred twenty only | Excel Worksheet Functions | |||
creating summary table of input from other worksheets | Excel Worksheet Functions | |||
VBA / Macro for creating new worksheets and new columns from existing worksheets | Excel Programming | |||
creating a pivot table w/ ranges from 2 worksheets | Excel Discussion (Misc queries) | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming |