VBA auto-sort issue

ptjimmons

New Member
Joined
Nov 28, 2016
Messages
5
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.

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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,143
Does the sort key needs to be one cell?

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

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,318
Hi,

I think a couple of things may not be quite right but please note that I have not actually tested this:
Code:
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top