Paste this into a standard module:
Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.
Option Explicit
Sub ConsolidateSheets()
Dim lRow&, lRow2&, vData, vSh, wsTgt As Worksheet
Dim CalcMode As XlCalculation
'Get a ref to the target sheet
Set wsTgt = ThisWorkbook.Sheets("Consolidate")
wsTgt.Cells.ClearContents: lRow2 = 1
'EnableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False
CalcMode = .Calculation: .Calculation = xlCalculationManual
End With 'Application
'Transfer data from the other sheets to the next empty row
On Error GoTo Cleanup
For Each vSh In ThisWorkbook.Sheets
If Not vSh = wsTgt Then
lRow = vSh.Range("A" & vSh.Rows.Count).End(xlUp).Row + 1
vData = vSh.Range("A1:BB" & lRow)
wsTgt.Cells(lRow2, "A").Resize(UBound(vData), UBound(vData, 2)) = vData
lRow2 = lRow2 + UBound(vData) '//set next blank row
End If
Next 'vSh
Cleanup:
Set wsTgt = Nothing
'DisableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False: .Calculation = CalcMode
End With 'Application
End Sub 'ConsolodateSheets
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion