VBA auto-sort issue

[ptjimmons]

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.

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!

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

 

[rpaulson]

Does the sort key needs to be one cell?

Range("A1:L" & lastRow).Sort key1:=Range("J1"), _
order1:=xlAscending, Header:=xlYes

 

[RickXL]

Hi,

I think a couple of things may not be quite right but please note that I have not actually tested this:

Option Explicit

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" & lr).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" & lr).Sort key1:=.Range("J1:J" & lr), _
           order1:=xlAscending, Header:=xlYes
    End With
End Sub

You use the With/End With construct but you did not add a dot (.) to the start of the Range definitions so they will both refer to the ActiveSheet.

Also, you have used LastRow when you probably meant lr. I always start my code with "Option Explicit". That forces me to define all my variables and it showed that LastRow was not defined. A closer check showed that you calculate lr and not LastRow.


regards,

 

[ptjimmons]

Yes this worked, thank you so much!

 

[RickXL]

No problem and thnks for letting me know.


Regards,