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

"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.

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 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
>
> "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.