"NxtRow = Cells(Rows.Count," giving me fits

[Howard]

I have used this very line of code successfully in other macros but here it errors out.

With cursor at the end of that line, Run DeBug - run to cursor gives me a yellow highlight. (NxtRow = Nothing)

Thanks.
Howard

Option Explicit

Sub Copy_All_Sheet_Sheet()
Dim WS As Worksheet
Dim NxtRow As Long
Dim Nws As Long

NxtRow = Cells(Rows.Count, "A").End(xlUp)(2).Row

For Nws = 2 To ThisWorkbook.Worksheets.Count
WS.UsedRange.Copy Sheets("Page 1").Range("A" & NxtRow)
Next Nws

End Sub

 

[Claus Busch]

Hi Howard,

Am Sun, 19 May 2013 06:45:40 -0700 (PDT) schrieb Howard:

I have used this very line of code successfully in other macros but here it errors out.

With cursor at the end of that line, Run DeBug - run to cursor gives me a yellow highlight. (NxtRow = Nothing)

try:

With Sheets("Page 1")
For Nws = 2 To ThisWorkbook.Worksheets.Count
NxtRow = .Cells(.Rows.Count, "A").End(xlUp)(2).Row
Sheets(Nws).UsedRange.Copy .Range("A" & NxtRow)
Next Nws
End With

Regards
Claus Busch

 

[Claus Busch]

Hi Howard,

Am Sun, 19 May 2013 17:25:07 +0200 schrieb Claus Busch:

With Sheets("Page 1")
For Nws = 2 To ThisWorkbook.Worksheets.Count
NxtRow = .Cells(.Rows.Count, "A").End(xlUp)(2).Row
Sheets(Nws).UsedRange.Copy .Range("A" & NxtRow)
Next Nws
End With

or try:

Sub Copy_All_Sheet_Sheet()
Dim Nws As Integer
Dim NxtC As Range

With Sheets("Page 1")
For Nws = 2 To ThisWorkbook.Worksheets.Count
Set NxtC = .Cells(.Rows.Count, "A").End(xlUp)(2)
Sheets(Nws).UsedRange.Copy NxtC
Next Nws
End With
End Sub


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Sun, 19 May 2013 17:25:07 +0200 schrieb Claus Busch:











or try:



Sub Copy_All_Sheet_Sheet()

Dim Nws As Integer

Dim NxtC As Range



With Sheets("Page 1")

For Nws = 2 To ThisWorkbook.Worksheets.Count

Set NxtC = .Cells(.Rows.Count, "A").End(xlUp)(2)

Sheets(Nws).UsedRange.Copy NxtC

Next Nws

End With

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Works dandy.

Thanks Claus.

Regards,
Howard