Copy data in multiple worksheet tabs into one worksheet
 

I have data in various tabs (some tabs have one row of data, others have
more) that I would like to move or copy into one worksheet. I would like to
use code to do this as some workbooks can have as much as 40 tabs while
others could have 10 tabs.

Copy data in multiple worksheet tabs into one worksheet
 

Sub make_summary()

Worksheets.Add befo=ThisWorkbook.Sheets(1)
Set SumSht = ActiveSheet
SumSht.Name = "Summary"

For Each sht In ThisWorkbook.Sheets
If sht.Name < "Summary" Then
SumLastRow = SumSht.Range("A" & Rows.Count).End(xlUp).Row
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("1:" & ShtLastRow).Copy _
Destination:=SumSht.Rows(SumLastRow + 2)
End If
Next sht

End Sub

"Bob" wrote:

I have data in various tabs (some tabs have one row of data, others have
more) that I would like to move or copy into one worksheet. I would like to
use code to do this as some workbooks can have as much as 40 tabs while
others could have 10 tabs.

Copy data in multiple worksheet tabs into one worksheet
 

This removes row 1 from all the worksheets and does not copy or move anything
to the "Summary" page.

"Joel" wrote:

Sub make_summary()

Worksheets.Add befo=ThisWorkbook.Sheets(1)
Set SumSht = ActiveSheet
SumSht.Name = "Summary"

For Each sht In ThisWorkbook.Sheets
If sht.Name < "Summary" Then
SumLastRow = SumSht.Range("A" & Rows.Count).End(xlUp).Row
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("1:" & ShtLastRow).Copy _
Destination:=SumSht.Rows(SumLastRow + 2)
End If
Next sht

End Sub

"Bob" wrote:

I have data in various tabs (some tabs have one row of data, others have
more) that I would like to move or copy into one worksheet. I would like to
use code to do this as some workbooks can have as much as 40 tabs while
others could have 10 tabs.