Macro for custom sort

[psrs0810]

can any help with a custom sort in a macro.

I recorded the one below, but it is not sorting and I need to change the range to be dynamic based on the info in column C starting in row 4

Application.AddCustomList ListArray:=Array("Medicare Subtotal", " Other Medicare HMO Subtotal”,”Senior Blue Subtotal”,”Medical Assistance Subtotal”,”Amerihealth Mercy Subtotal”,”CCBH Subtotal”,”Gateway Subtotal”,”MedPlus Three Rivers Subtotal”,”Aetna Subtotal”,”Amerihealth Admin Subtotal”,”Auto Subtotal”,”BHP Subtotal”,”BHP Employee Subtotal”,”Blue Shield Subtotal”,”Capital Blue Cross Subtotal”,”Cigna Subtotal”,”Health America Subtotal”,”Independence BC Subtotal”,”Keystone Central Subtotal”,”Keystone East Subtotal”,”Self-Pay Subtotal”,”United Healthcare Subtotal”,”Workers Comp Subtotal", _<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
"Senior Blue")
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A140") _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Medicare,Medicare HMO,Senior Blue", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:AL140")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[psrs0810]

Sorry, i am not that good with plugging something in. Would you assist?

 

[Andrew Poulsom]

Try:

    Dim LR As Long
    With ActiveWorkbook.Worksheets("Sheet2")
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
    End With
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("C3:C" & LR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Medicare Subtotal, Other Medicare HMO Subtotal,Senior Blue Subtotal,Medical Assistance Subtotal,Amerihealth Mercy Subtotal,CCBH Subtotal,Gateway Subtotal,MedPlus Three Rivers Subtotal,Aetna Subtotal,Amerihealth Admin Subtotal,Auto Subtotal,BHP Subtotal,BHP Employee Subtotal,Blue Shield Subtotal,Capital Blue Cross Subtotal,Cigna Subtotal,Health America Subtotal,Independence BC Subtotal,Keystone Central Subtotal,Keystone East Subtotal,Self-Pay Subtotal,United Healthcare Subtotal,Workers Comp Subtotal,Senior Blue" _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A3:O" & LR)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[psrs0810]

I don't get it. It works when I sort manually and when I recorded it and ran the recorded verson. As soon as the range is changed to dynamic, it does not work

 

[Andrew Poulsom]

How very odd.