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!

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" <(E-Mail Removed)> wrote in message
news:5B243E1D-8F4B-4745-AA43-(E-Mail Removed)...
> 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!

See
http://www.rondebruin.nl/copy2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"JEFF" <(E-Mail Removed)> wrote in message news:5B243E1D-8F4B-4745-AA43-(E-Mail Removed)...
> 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!

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
(E-Mail Removed)

"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!

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" <(E-Mail Removed)> wrote in message
> news:5B243E1D-8F4B-4745-AA43-(E-Mail Removed)...
> > 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!
>
>
>