How to copy last 30 rows (Run-time error 1004)

[sim89]

Hi,
I'm trying to get a macro to copy the last 30 rows of data to a different sheet.

I have the code below but I am getting "Run-time error 1004: Application-defined or Object-defined error"

I've tried a few different things but can't work out what's causing the error.

Sub Last30()
Dim lastrow As Long

With Sheets("Stage1")
lastrow = Sheets("Stage1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Stage1").Range("A" & lastrow - 29 & ":S" & lastrow).Copy Sheets("Stage1Last30").Range("A2")
End With

With Sheets("Stage2")
lastrow = Sheets("Stage2").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Stage2").Range("A" & lastrow - 29 & ":AA" & lastrow).Copy Sheets("Stage2Last30").Range("A2")
End With

End Sub

Any help would be really appreciated.
Thanks

 

[sim89]

Sorry my code tags didn't work

 

[gallen]

Hello and welcome.

On what line do you get the error?

Do all the following exist? Ensure the spelling is exactly right:

Sheets("Stage1")
Sheets("Stage1Last30")
Sheets("Stage2")
Sheets("Stage2Last30")

Also, ensure the sheets aren't protected.

 

[sim89]

Hi gallen, thanks for your reply

I get the error on the line

Sheets("Stage1").Range("A" & lastrow - 29 & ":S" & lastrow).Copy Sheets("Stage1Last30").Range("A2")

I've checked the sheet names are spelled correctly, and none of the sheets are protected

 

[Fluff]

What is the value of lastrow when you get the error?
Also your With Statements are currently redundant, they should be like

With Sheets("Stage1")
   lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
   .Range("A" & lastrow - 29 & ":S" & lastrow).Copy Sheets("Stage1Last30").Range("A2")
End With