getting a Compile Error

[RAJESH1960]

Hello guys
I am trying to edit JohnnyL's code and add one more level in the sort data but I keep getting an error. Can someone please tell me what am I doing wrong here.?
JohnnyL's code

'   RANGE SORTER ... Most important column to least important column 3,6,2
    With wsDestination
        .Range("A2:O" & .Range("B" & Rows.Count).End(xlUp).Row).Sort _
                Key1:=.Range("C2"), Order1:=xlAscending, _
                Key2:=.Range("F2"), Order1:=xlAscending, _
                Key3:=.Range("B2"), Order1:=xlAscending, Header:=xlNo                               '   Sort the destination sheet by various columns
    End With

I tried doing this but I am getting a compile error.

RANGE SORTER ... Most important column to least important column 3,6,2
    With wsDestination
        .Range("A2:O" & .Range("B" & Rows.Count).End(xlUp).Row).Sort _
                Key1:=.Range("C2"), Order1:=xlAscending, _
                Key2:=.Range("G2"), Order1:=xlAscending, _
                Key3:=.Range("H2"), Order1:=xlAscending, Header:=xlNo                               '   Sort the destination sheet by various columns
                Key4:=.Range("B2"), Order1:=xlAscending, Header:=xlNo                               '   Sort the destination sheet by various columns
    End With

 

[RAJESH1960]

I just corrected the above as

RANGE SORTER ... Most important column to least important column 3,6,2
    With wsDestination
        .Range("A2:N" & .Range("B" & Rows.Count).End(xlUp).Row).Sort _
                Key1:=.Range("C2"), Order1:=xlAscending, _
                Key2:=.Range("G2"), Order1:=xlAscending, _
                Key3:=.Range("H2"), Order1:=xlAscending, _
                Key4:=.Range("B2"), Order1:=xlAscending, Header:=xlNo                               '   Sort the destination sheet by various columns
    End With

but still I am getting a compile error at

                Key4:=.Range("B2"), Order1:=xlAscending, Header:=xlNo                               '   Sort the destination sheet by various columns

stating named argument not found.

 

[Domenic]

You won't be able to sort more than 3 columns using the Sort method of the Range object. Use the Sort object instead...

    Dim sortRange As Range
    With wsDestination
        Set sortRange = .Range("A2:N" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End With
   
    With wsDestination.Sort.SortFields
        .Clear
        .Add2 Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add2 Key:=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add2 Key:=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add2 Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
   
    With wsDestination.Sort
        .SetRange sortRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Hope this helps!

 

[RAJESH1960]

You won't be able to sort more than 3 columns using the Sort method of the Range object. Use the Sort object instead...

    Dim sortRange As Range
    With wsDestination
        Set sortRange = .Range("A2:N" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End With
  
    With wsDestination.Sort.SortFields
        .Clear
        .Add2 Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add2 Key:=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add2 Key:=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add2 Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
  
    With wsDestination.Sort
        .SetRange sortRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Hope this helps!

It really helped. Thanks Domenic. Now I can add or remove levels and then too it will work I hope. Will try it out.

 

[Domenic]

It really helped. Thanks Domenic.

You're very welcome, glad I could help.

Now I can add or remove levels and then too it will work I hope. Will try it out.

It looks like you can have up to 64 levels...

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

Cheers!

 

[RAJESH1960]

up to 64 levels

Ok will keep in mind. Thanks