Search code examples
excelvbarangeworksheet

How to reference UsedRange in non active sheet in MS EXCEL VBA

The code works fine when the sheet with the data to be sorted is active (sheet2). But I actually have the macro button on a different sheet (sheet1). This is the code in the module:

    Sheet2.UsedRange.Sort Key1:=Range("O1"), Key2:=Range("L1"), Key3:=Range("I1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlAscending, Order3:=xlDescending

When I run the macro from the button in sheet1 (ie sheet 1 is now the active sheet, but not with the data that needs sorting) I get Run-time error 1004, the sort reference is not valid. This is because there's no data on the now active sheet (sheet1).

How can I reference the usedrange in sheet 2 properly so it will sort the data despite the sheet being inactive?

Solution

  • Your problem isn't Sheet2.UsedRange, but the unqualified Range calls for the key parameters, which means that implicitly these are ActiveSheet.Range("O1"), ActiveSheet.Range("L1") and so on.

    Either add Sheet2 before each Range call, or perhaps use a With block and make sure to add a period . before each Range call, as well as before UsedRange.

    With Sheet2
    .UsedRange.Sort Key1:=.Range("O1"), Key2:=.Range("L1"), Key3:=.Range("I1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlAscending, Order3:=xlDescending
End With