Excel VBA Range.sort alternative?

[khlau]

Hello all,

Wondering if someone could help? I am trying to sort two tabs in my workbook (three total tabs) which I am able to do with the below:

Sub SortAllSheets()


Sheet1.Activate


Dim lrow As Long
lrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:R" & lrow).Sort key1:=Range("J1:J" & lrow), order1:=xlDescending, Header:=xlYes


Sheet3.Activate


Dim lrow2 As Long
lrow2 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:H" & lrow2).Sort key1:=Range("E1:E" & lrow2), order1:=xlDescending, Header:=xlYes



End Sub

However I am wondering if it would be possible to achieve this without the "Sheet1.Activate" & "Sheet3.Activate" commands? and what the corresponding code would be?

Thanks.

 

[RoryA]

Yes, you can do it like this:

Sub SortAllSheets()

    With Sheet1
        Dim lrow As Long
        lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A1:R" & lrow).Sort key1:=.Range("J1:J" & lrow), order1:=xlDescending, Header:=xlYes
    End With

    With Sheet3
        Dim lrow2 As Long
        lrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A1:H" & lrow2).Sort key1:=.Range("E1:E" & lrow2), order1:=xlDescending, Header:=xlYes
    End With

End Sub