"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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[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

[Howard]

Works dandy.

Thanks Claus.

Regards,
Howard