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.

Works for me in 2003.


Gord Dibben MS Excel MVP

On Fri, 15 Feb 2008 08:29:05 -0800, Bob wrote:

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.

I get run-time error '438':
object doesn't support this property or method on highlighted

ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row

also 2003 SP2

"Gord Dibben" wrote:

Works for me in 2003.


Gord Dibben MS Excel MVP

On Fri, 15 Feb 2008 08:29:05 -0800, Bob wrote:

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.