Custom sort using named ranges

[jwarren73]

Hello - I'm having trouble developing a custom sort code that uses values in a named range for the sort options, and was hoping that someone could point out what I overlooked - I just started using sort codes yesterday, and the Range.Sort syntax provided by Microsoft is less than helpful..

I'm using the following code to copy and sort data after new entries are added through a userform:

    Sheet5.Range("L2:L300").Copy
    With Sheet5
        .Range("R2").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        .Range("S2:S300").Sort Key1:=.Range("S2"), Order1:=xlAscending
    End With

That code works perfectly for basic sorting. However, I'm also trying to re-sort a related group of data that is organized by a custom sort - the following code does not work:

Sheet5.Range("L2:R300").Sort Key1:=Sheet5.Range("Q2"), Order1:=xlAscending, CustomOrder:=MasterPrimaryLocation, Key2:=Sheet5.Range("R2"), Order2:=xlAscending, CustomOrder:=Shift, Key3:=Sheet5.Range("P2"), Order3:=xlAscending, CustomOrder:=MasterPosition, Key4:=Sheet5.Range("L2"), Order4:=xlAscending

Named ranges are MasterPrimaryLocation, Shift, and MasterPosition. These ranges are set dynamically, with the intent of removing any need to adjust code as options in those ranges will change over time. Using quotations around the range names doesn't work as it looks for those names to actually sort with, and removing the quotations appears to cause issues as those names are not defined within the VBA - I'm not sure how to tell VBA to look for that information in the Name Manager.

Any suggestions?

 

[cronos2546]

I have not used the .Sort method before, but it appears that it only accepts 3 keys from this MSDN article, while you supply 4.

https://msdn.microsoft.com/en-us/library/office/ff840646.aspx

 

[jwarren73]

Hmmm

Changing CustomOrder:=Shift to CustomOrder:="AM,PM" results in a Named Argument not found error... so I must be missing something else....

 

[jwarren73]

Removing 4th sort option has same result..