Sort Table by Multiple Keys - VBA

[melodramatic]

I am having a tough time getting a sort in my macro. Here's what I currently have:

        Range("Table1[#All]").Select
            ActiveWorkbook.Worksheets("Entry").Sort Key1:=Range("Table1[[#All],[DCN]]"), _
                Order1:=xlAscending, _
            Key2:=Range("Table1[#All],[Date]]"), _
                Order2:=xlAscending, _
                SortOn:=xlSortOnValues, _
                DataOption2:=xlSortTextAsNumbers, _
                Header:=xlYes

Which gives me the "Run-time error '1004'" Method 'Range' of object '_Global' failed.

If I run the sort manually, here's what I'm trying to do:

Can someone help me figure out what I'm doing wrong?

 

[duggie33]

Hi melodramatic,

I think you have to do a ".Sort.SortFields.Add2" for each key. I could be mistaken. Maybe record a macro sorting two levels to see what the recorder does. Undoubtedly, you will have to clean-up the code.

I hope that helps,

Doug

 

[Fluff]

You are mixing up the two different types of sort, try

        Range("Table1[#All]").Sort Key1:=Range("Table1[DCN]"), _
                Order1:=xlAscending, _
            Key2:=Range("Table1[Date]"), _
                Order2:=xlAscending, _
                dataoption1:=xlSortOnValues, _
                DataOption2:=xlSortTextAsNumbers, _
                Header:=xlYes

 

[melodramatic]

And that fixed my problem. Thank you, Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.