I had my macro working and then I change my sheet names and its giving me a Run-time error '9'. I know it has something to do with the name change but I can not figure out how to make the macro only look for the codename not the sheet name. Any help would be greatly appreciated. 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
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