VBA Sort multiple columns

[maclachlan19]

I'm trying to run some code to sort multiple columns at once. I get an error:
Compile error: Named argument not found

Range("Managers").Sort Key1:=Range("Q4"), Order1:=xlDescending, _
                        Key2:=Range("G4"), Order2:=xlDescending, _
                        Key3:=Range("H4"), Order3:=xlAscending, _
                        Key4:=Range("F4"), Order4:=xlDescending, _
                        Key5:=Range("I4"), Order5:=xlAscending, _
                        Header:=xlYes

 

[Herakles]

You can only sort by three columns at a time.

You will need to do it in two stages.

 

[maclachlan19]

That makes sense on why in testing I could get the code to work with only 2 columns.
The problem is I can't break this into 2 stages as I need the sort to be done at the same time. If I use 2 stages the sort from the 1st stage is overridden by the 2nd stage.

 

[maclachlan19]

Forgot to add, I can't use Autofilter as the sheet is protected and the sort doesn't work on a protected sheet.

 

[mikerickson]

You could do something like

With Range("Managers")
    .Sort (key1:=Range("I4"), Order1:= xlAscending)
    .Sort (key1:=Range("F4"), Order1:= xlDescending)
    .Sort (key1:=Range("H4"), Order1:= xlAscending)
    .Sort (key1:=Range("G4"), Order1:= xlDescending)
    .Sort (key1:=Range("Q4"), Order1:= xDescending)
End With

Note that the order of the keys of the 5 sorts is in reverse order of the keys in the single sort attempt.

 

[maclachlan19]

Thanks for the quick responses Mike.
When I run this code I get a "Compile error: Syntax error"
Do I use Key1 for all 5 lines?

 

[mikerickson]

That error It may be the parenthesis.
What I posted is 5 different sorts, each with its own key, i.e. key1.

 

[maclachlan19]

I tried this below but same error, but perhaps I misunderstood your latest comment

Private Sub CommandButton2_Click()
With Range("Managers")
    ,Sort key1:=Range("I4"), Order1:= xlAscending _
    ,Sort key1:=Range("F4"), Order1:= xlDescending _
    ,Sort key1:=Range("H4"), Order1:= xlAscending _
    ,Sort key1:=Range("G4"), Order1:= xlDescending _
    ,Sort key1:=Range("Q4"), Order1:= xDescending _
End With

 

[Herakles]

Yes, take out the parenthesis.

The last order should be xlDescending.

 

[maclachlan19]

my mistake,
I'm still getting the "Compile error: Syntax error" .

Private Sub CommandButton2_Click()
With Range("Managers")
    ,Sort key1:=Range("I4"), Order1:= xlAscending _
    ,Sort key1:=Range("F4"), Order1:= xlDescending _
    ,Sort key1:=Range("H4"), Order1:= xlAscending _
    ,Sort key1:=Range("G4"), Order1:= xlDescending _
    ,Sort key1:=Range("Q4"), Order1:= xlDescending _
End With