VBA 1004 Error when using a Variable in the Range

[ConestogaKid]

I am trying to see if it is possible to paste specific data below the data on another sheet. I am sure I have no clue how to correctly use select and activesheet and they probably slow down all of my code.
If you have any tips on how to get this to work correctly or just write it better in general. When I use a specific range, ("A786" is listed as the one currently) I don't have any errors and it works just fine, however when I try to use Cell(last_row2, 1) it gives the 1004 errors.
Sheet to copy data from: CLA_MT (CA)
Sheet to paste data to: SalesData

If you need any other information please let me know.

Sub CLAMT_Copy()
Dim last_row1, last_row2 As Long

Sheets("CLA_MT (CA)").Select
ActiveSheet.Name = "CLA_MT (CA)"

'Get last row CLA_MT (CA)
last_row1 = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("SalesData").Select
ActiveSheet.Name = "SalesData"

'get last row in SalesData to paste info below it
last_row2 = Cells(Rows.Count, 1).End(xlUp).Row
last_row2 = last_row2 + 1
'Cells(786, 1) = last_row2 'confirming pasting in the correct row

Sheets("CLA_MT (CA)").Select
ActiveSheet.Name = "CLA_MT (CA)"

'copy data reformatted data from CLA_MT
Worksheets("CLA_MT (CA)").Range(Cells(2, 15), Cells(last_row1, 24)).Copy

'Pastes to the end of the SalesData Sheet
Worksheets("SalesData").Range(Cells(last_row2, 1)).PasteSpecial Paste:=xlPasteValues
'Worksheets("SalesData").Range("A786").PasteSpecial Paste:=xlPasteValues 'this works but i need to set it to a variable range to adjust for new data
Application.CutCopyMode = False

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CLAMT_Copy()
   Dim last_row1 As Long
   
   With Sheets("CLA_MT (CA)")
      last_row1 = .Cells(Rows.count, 1).End(xlUp).Row
   'copy data reformatted data from CLA_MT
      .Range(.Cells(2, 15), Cells(last_row1, 24)).Copy
   End With
   'Pastes to the end of the SalesData Sheet
   With Worksheets("SalesData")
      .Range("A" & Rows.count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   End With
End Sub

 

[ConestogaKid]

Hi & welcome to MrExcel.
How about

Sub CLAMT_Copy()
   Dim last_row1 As Long
  
   With Sheets("CLA_MT (CA)")
      last_row1 = .Cells(Rows.count, 1).End(xlUp).Row
   'copy data reformatted data from CLA_MT
      .Range(.Cells(2, 15), Cells(last_row1, 24)).Copy
   End With
   'Pastes to the end of the SalesData Sheet
   With Worksheets("SalesData")
      .Range("A" & Rows.count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   End With
End Sub

That worked! thank you so much! I spent way too long trying to do this. I dont quite understand your code yet, but I will look into the offset and how you used ranges and the with sheets.

 

[Fluff]

You're welcome & thanks for the feedback.