Sort by level (macro not working)

[gomes123]

I'm trying to automate the sort function in excel (by levels).
So I would like to first sort Column B by A-Z
Then the next level is sort Column N from oldest to newest date

The problem with the macro below, is it sorts Column B fine (A-Z), but then it doesn't apply the "level" criteria, and it just jumbles up the dates in Column N (from oldest to newest), and it doesn't tally with the data in Column B. How would I fix this? Most appreciated, thanks.

Sub SortColumnsBAndN()
    'Sort Column B by A-Z
    Columns("B:B").Sort key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
    
    'Sort Column N by oldest to newest date
    Columns("N:N").Sort key1:=Range("N2"), Order1:=xlAscending, Header:=xlYes, _
    MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers, _
    Header:=xlYes
End Sub

 

[myall_blues]

You need to add all the sort fields first, and then sort the whole thing by the two keyfields.

For example.

Sub SortColumnsBAndN()
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("B2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("N2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B:N")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Regards

Murray

 

[gomes123]

Thanks for your help, but I'm getting a runtime error 438 when I run your code, on the line

 SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

. Is there something I'm missing?

 

[MARK858]

Try changing SortFields.Add2 to SortFields.Add

 

[Fluff]

@myall_blues when using the sheet.Sort method you must always delete any previous sorts, otherwise you end up getting errors when opening the workbook.

 

[MARK858]

The OP is also using 2007 which is why I mentioned the Add2

 

[myall_blues]

@myall_blues when using the sheet.Sort method you must always delete any previous sorts, otherwise you end up getting errors when opening the workbook.

Thank you both for the advice.

 

[gomes123]

You need to add all the sort fields first, and then sort the whole thing by the two keyfields.

For example.

Sub SortColumnsBAndN()
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("B2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("N2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B:N")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Regards

Murray

Thanks, after changing to Add from Add2, it works!

Try changing SortFields.Add2 to SortFields.Add

Thanks! It worked after changing it to Add, from Add2.