copy worksheet tab data into one sheet
 

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.

copy worksheet tab data into one sheet
 

Either sht.Index or sht.Name with Sheets(?)

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

"Bob" wrote:

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.

copy worksheet tab data into one sheet
 

Here is another way to handle it

Sub sht()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If Not sht Is Nothing Then
With sht
lr = .Range("A" & Rows.Count).End(xlUp).Row
MsgBox lr & " Sheets(" & sht.Name & ")"
End With
End If
Next

"Bob" wrote:

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.

copy worksheet tab data into one sheet
 

But why have to make a revision to original?


Gord

On Fri, 15 Feb 2008 15:42:00 -0800, JLGWhiz
wrote:

Either sht.Index or sht.Name with Sheets(?)

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

"Bob" wrote:

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.

copy worksheet tab data into one sheet
 

I don't know, Gord. It is kind of weird. I thought the original should
work, but when I ran a simulation just to check, I got the error also. Then
after you questioned why, I can't get the error to repeat. Now I am
wondering how I got the error in the first place. I think I will just go to
bed.

"Gord Dibben" wrote:

But why have to make a revision to original?


Gord

On Fri, 15 Feb 2008 15:42:00 -0800, JLGWhiz
wrote:

Either sht.Index or sht.Name with Sheets(?)

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

"Bob" wrote:

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.

copy worksheet tab data into one sheet
 

Thanks for the feedback.

Nothing wrong with making a change if it will ensure stability.

Thta's two of you who got some sort of error with the original.


Gord

On Fri, 15 Feb 2008 20:01:00 -0800, JLGWhiz
wrote:

I don't know, Gord. It is kind of weird. I thought the original should
work, but when I ran a simulation just to check, I got the error also. Then
after you questioned why, I can't get the error to repeat. Now I am
wondering how I got the error in the first place. I think I will just go to
bed.

"Gord Dibben" wrote:

But why have to make a revision to original?


Gord

On Fri, 15 Feb 2008 15:42:00 -0800, JLGWhiz
wrote:

Either sht.Index or sht.Name with Sheets(?)

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

"Bob" wrote:

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.