VBA not working after sheet name changed

[Dustin R]

I had my macro working and then I change the sheet names and now I am getting a Run-time error '9'. I have tried many option to fix it and no luck. I hope some one can help me with his issue. Here is my macro:

Sub copycolums()
Dim lastrow As Long, erow As Long


lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 9) = "Active" Then
If Sheet1.Cells(i, 7) >= 1800 Then


Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 3)
Sheet1.Cells(i, 2).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 1)


Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 2)


Sheet1.Cells(i, 11).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 4)


Sheet1.Cells(i, 15).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 5)
End If
End If


Next i


Application.CutCopyMode = False
Sheet2.Columns.AutoFit
Range("A1").Select


End Sub

 

[Scott Huish]

That's because you hardcoded the name "Sheet2". If you change the name of that then it won't exist.
If you want to refer to the sheet in the 2nd position, instead of Worksheets("Sheet2"), use Worksheets(2)