Copy data in multiple worksheet tabs into one worksheet

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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.
 
Sub make_summary()

Worksheets.Add before:=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
 
This removes row 1 from all the worksheets and does not copy or move anything
to the "Summary" page.
 
Back
Top