Hello everyone, I'm working on a macro that will enable me to copy data from one sheet to two other sheets, based on a specific value in column L, and then sorting oldest to newest based on the time/date entered in column J. The copy/paste element works well, but I have been having trouble with the sorting portion, getting "Run-time error '1004': Method 'Range' of object '_Global' failed", and lines 21 and 22 highlighted.
I have been poking around solutions people with similar issues have found, and I still can't put my finger on the issue. There seems to be a problem with the ranges or sheets referenced, but the sheet names match what is in the code. If anyone could take a look at my code and help me figure it out that would be great. Thanks!
Code:
Range("A1:L" & lastRow).Sort key1:=Range("J1:J" & lr), _
order1:=xlAscending, Header:=xlYes
I have been poking around solutions people with similar issues have found, and I still can't put my finger on the issue. There seems to be a problem with the ranges or sheets referenced, but the sheet names match what is in the code. If anyone could take a look at my code and help me figure it out that would be great. Thanks!
Code:
Sub EGS_CVS_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("template").Cells(Rows.Count, "L").End(xlUp).Row
For r = lr To 2 Step -1
Select Case Sheets("template").Range("L" & r).Value
Case Is = "1a"
lr2 = Sheets("EGS lines").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("template").Rows(r).Copy Destination:=Sheets("EGS lines").Range("A" & lr2 + 1)
Case Is = "1b"
lr2 = Sheets("CVS lines").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("template").Rows(r).Copy Destination:=Sheets("CVS lines").Range("A" & lr2 + 1)
End Select
Next r
With Sheets("EGS lines")
lr = .Cells(Rows.Count, "L").End(xlUp).Row
Range("A1:L" & lastRow).Sort key1:=Range("J1:J" & lr), _
order1:=xlAscending, Header:=xlYes
End With
With Sheets("CVS lines")
lr = .Cells(Rows.Count, "L").End(xlUp).Row
Range("A1:L" & lastRow).Sort key1:=Range("J1:J" & lr), _
order1:=xlAscending, Header:=xlYes
End With
End Sub