Dear JLatham

This is totally awesome. Worked like a charm.

A quick explanation of why I'm doing this - using this idea to workaround
the limitations of multiple consolidation ranges using pivot tables.

Two more favors to ask. Could you add the funcionality to clear all rows on
the summary sheet, except for the first, each time the macro is run.

As one or more of the sheets may contain pivot tables or other data, can I
name the worksheets to include in the row concatenation?

Sorry to trouble you with this but I didn't think this through thoroughly
before asking my original question.

Thanks

Kevin





"JLatham" wrote:

What do you mean by concatenate in this context? Do you want the summary
sheet to contain all of the not-empty rows of data from all of the other
sheets? Or are you somehow wanting some kind of actual summary of the data
on the others?

If you do want all data from all other sheets, then the code below should
help. Be sure that data is not filtered on any sheets to definitely capture
all data. I took the easy way out and assumend that there is at least 1
column that will always have something in it on the sheets on any row that
needs to be copied. Since you said all sheets had same format, I think this
has a good chance of being true?

Sub BuildSummarySheet()
'note that this does not clear existing entries
'from the summary sheet, so multiple runs of
'it will result in replicated entries - you
'should manually clear all previous entries
'before running this code

'this all depends on there being one
'column that will always have data in it
'on any row that has data change this
'Const value to indicate that column
Const testCol = "A"
Dim summaryWS As Worksheet
Dim anyWS As Worksheet
Dim lastRow As Long
Dim testList As Range
Dim anyTestCell As Range
Dim row2Copy As Range
Dim rowPointer As Long

'change this to the summary sheet's name
Set summaryWS = ThisWorkbook.Worksheets("Sheet1")
'to improve performance
Application.ScreenUpdating = False
'begin the actual work
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name summaryWS.Name Then
lastRow = anyWS.Range(testCol & Rows.Count).End(xlUp).Row
If lastRow 1 Then
Set testList = anyWS.Range(testCol & "2:" & _
testCol & lastRow)
For Each anyTestCell In testList
If Not IsEmpty(anyTestCell) Then
Set row2Copy = anyWS.Rows(anyTestCell.Row & _
":" & anyTestCell.Row)
row2Copy.Copy
summaryWS.Range(testCol & Rows.Count).End(xlUp). _
Offset(1, 0).PasteSpecial xlPasteValues
'if you also want formats copied, then
'remove the ' in front of this next line of code
'summaryWS.Range(testCol & Rows.Count).End(xlUp). _
PasteSpecial xlPasteFormats
End If ' end IsEmpty test block
Next ' end anyTestCell loop
End If 'end test for lastRow
End If ' end test for sheet name matchup
Next ' end anyWS loop
'do some housekeeping
Set testList = Nothing
Set row2Copy = Nothing
Set summaryWS = Nothing
MsgBox "Summary Sheet Build Completed", vbOKOnly, "Job Done"

End Sub


"wild turkey no9" wrote:

I have data all structured in the same way (same column headings) across
multiple sheets within the same workbook. Is there an easy way to concatenate
all of the data, without any empty rows, into one summary sheet in the
workbook?

Thanks in advance.

Kevin