Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data to one worksheet
Hello,
I have a workbook with 50+ worksheets that have data in the exact same place and format. I would like to take the contents of each worksheet and consolidate it into a single worksheet, working downwards. For example, the data in all sheets is found in A1:C3. I would like this consolidated worksheet to have the contents of sheet one be in the same A1:C3, but sheet two's data would go directly underneath, into cells A4:C6, sheet three's data into cells A7:C9.... and so on. Note: 1. The number of original worksheets could grow from 50 to 100 2. The consolidated data could be a new workbook, a new worksheet, or on the first worksheet Thanks a lot in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data to one worksheet
The following will copy the range A1:C3 from every sheet onto the currently
active sheet, but not itself, offsetting the data by the value in xstep. xlocate define the first copy destination. Sub ConsSheets() Dim wS As Worksheet Dim xstep As Integer, xLocate As Long xstep = 3: xLocate = 4 For Each wS In ActiveWorkbook.Worksheets If wS.Index < ActiveSheet.Index Then wS.Range("A1:C3").Copy Destination:=ActiveSheet.Cells(xLocate, 1) xLocate = xLocate + xstep End If Next End Sub -- Cheers Nigel "JEFF" wrote in message ... Hello, I have a workbook with 50+ worksheets that have data in the exact same place and format. I would like to take the contents of each worksheet and consolidate it into a single worksheet, working downwards. For example, the data in all sheets is found in A1:C3. I would like this consolidated worksheet to have the contents of sheet one be in the same A1:C3, but sheet two's data would go directly underneath, into cells A4:C6, sheet three's data into cells A7:C9.... and so on. Note: 1. The number of original worksheets could grow from 50 to 100 2. The consolidated data could be a new workbook, a new worksheet, or on the first worksheet Thanks a lot in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data to one worksheet
WORKS! thanks
"Nigel" wrote: The following will copy the range A1:C3 from every sheet onto the currently active sheet, but not itself, offsetting the data by the value in xstep. xlocate define the first copy destination. Sub ConsSheets() Dim wS As Worksheet Dim xstep As Integer, xLocate As Long xstep = 3: xLocate = 4 For Each wS In ActiveWorkbook.Worksheets If wS.Index < ActiveSheet.Index Then wS.Range("A1:C3").Copy Destination:=ActiveSheet.Cells(xLocate, 1) xLocate = xLocate + xstep End If Next End Sub -- Cheers Nigel "JEFF" wrote in message ... Hello, I have a workbook with 50+ worksheets that have data in the exact same place and format. I would like to take the contents of each worksheet and consolidate it into a single worksheet, working downwards. For example, the data in all sheets is found in A1:C3. I would like this consolidated worksheet to have the contents of sheet one be in the same A1:C3, but sheet two's data would go directly underneath, into cells A4:C6, sheet three's data into cells A7:C9.... and so on. Note: 1. The number of original worksheets could grow from 50 to 100 2. The consolidated data could be a new workbook, a new worksheet, or on the first worksheet Thanks a lot in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data to one worksheet
See
http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "JEFF" wrote in message ... Hello, I have a workbook with 50+ worksheets that have data in the exact same place and format. I would like to take the contents of each worksheet and consolidate it into a single worksheet, working downwards. For example, the data in all sheets is found in A1:C3. I would like this consolidated worksheet to have the contents of sheet one be in the same A1:C3, but sheet two's data would go directly underneath, into cells A4:C6, sheet three's data into cells A7:C9.... and so on. Note: 1. The number of original worksheets could grow from 50 to 100 2. The consolidated data could be a new workbook, a new worksheet, or on the first worksheet Thanks a lot in advance! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data to one worksheet
hi jeff
try this dim temparray() dim totalworksheets as integer totalworksheets=activeworkbook.worksheets.count dim n as integer n=1 for each wk in activeWorkbook worksheets(n).activate rowcnt=activesheet.usedrange.rows.count colcnt=activesheet.usedrange.columns.count redim preserve temparray(rowcn,colcnt) with activesheet for x= 1 to rowcnt for y= 1 to colcnt temparray(x,y)=.cells(x,y) next y next x end with worksheets(totalworksheets+1).activate with activesheet for x1 = 1 to ubound(temarray,1) for y1=ubound(temparray,2) .cells(x1,y1)=temparray(x1,y1) next y1 next x1 end with n=n+1 redim temparray() next -- hemu "JEFF" wrote: Hello, I have a workbook with 50+ worksheets that have data in the exact same place and format. I would like to take the contents of each worksheet and consolidate it into a single worksheet, working downwards. For example, the data in all sheets is found in A1:C3. I would like this consolidated worksheet to have the contents of sheet one be in the same A1:C3, but sheet two's data would go directly underneath, into cells A4:C6, sheet three's data into cells A7:C9.... and so on. Note: 1. The number of original worksheets could grow from 50 to 100 2. The consolidated data could be a new workbook, a new worksheet, or on the first worksheet Thanks a lot in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros for Consolidating data in worksheet 1 on worksheet 2 | Excel Programming | |||
consolidating data in an excel worksheet | Excel Discussion (Misc queries) | |||
Consolidating Sales Data to Summary Worksheet | Excel Worksheet Functions | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming | |||
Help with worksheet consolidating | Excel Programming |