Hi, the

Ron de Bruin's site provides the following wonderful code, which reads
through all of the sheets in a workbook and then creates a new sheet that
consolidates all of the rows on them into one new sheet (called "Summary," in
this case). I've left his explanatory comments in:

Sub BuildSummarySheet()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Summary" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary"

'Fill in the start row
StartRow = 5

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets exept the RDBMerge worksheet and the
'Information worksheet, you can ad more sheets to the array if you
want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "LOOKUPS"), 0)) Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Summary Sheet
to continue."
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


I'm not sure, however, how to include a head row on the newly-created
summary. Rows 1 through 4 of all of my subsidiary sheets are identical. How
can I tell the macro to copy and paste the heeads on the first iteration of
the loop, but not thereafter? The subsidiary sheets are not named the same
thing--and in fact, their names may change--but the column rows/heads will
always be the same.

Can anyone help? Thanks in advance!

hi
if its just a one shot deal, i would stay out of the loop.

add this line to the dims...
dim ash as worksheet

add this line just before the summary sheet is added. this will mark the
activesheet. if all the sheets have the same header, it should make no
difference which sheet.....
set ash = activesheet

Add this just after the summary sheet has been added......
ash.Activate 'go back to last sheet
Range(Range("A1"), Range("A1").Offset(3, 8)).Copy _
Destination:=Sheets("Summary").Range("A1")
Sheets("summary").Activate 'go back to summary sheet

the offset above assumes a header group of 4 rows x 9 columns. adjust if
needed.
the next line should be ron's code...startrow = 5

post back if problems

Regards
FSt1

"Wart" wrote:

Hi, the

Ron de Bruin's site provides the following wonderful code, which reads
through all of the sheets in a workbook and then creates a new sheet that
consolidates all of the rows on them into one new sheet (called "Summary," in
this case). I've left his explanatory comments in:

Sub BuildSummarySheet()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Summary" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary"

'Fill in the start row
StartRow = 5

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets exept the RDBMerge worksheet and the
'Information worksheet, you can ad more sheets to the array if you
want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "LOOKUPS"), 0)) Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Summary Sheet
to continue."
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


I'm not sure, however, how to include a head row on the newly-created
summary. Rows 1 through 4 of all of my subsidiary sheets are identical. How
can I tell the macro to copy and paste the heeads on the first iteration of
the loop, but not thereafter? The subsidiary sheets are not named the same
thing--and in fact, their names may change--but the column rows/heads will
always be the same.

Can anyone help? Thanks in advance!