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

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim n As Long
    Dim LR As Long
    Set Sh = ActiveWorkbook.Worksheets("Sheet1")
    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", "Senior Blue")
    n = Application.GetCustomListNum(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", "Senior Blue"))
    With Sh
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        Range("C4:AL" & LR).Sort Key1:=.Range("C4"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=n, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
End Sub

 

[psrs0810]

Everything looks great, but I am getting a runtime error at:
Range("C4:AL" & LR).Sort Key1:=.Range("C4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=n, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1

I am not sure what is causing this error.

suggestions?

 

[psrs0810]

the error says "the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By boc isn't the same or blank"

 

[Andrew Poulsom]

Sorry it should be:

.Range("C4:AL" & LR).Sort Key1:=.Range("C4"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=n, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 

[psrs0810]

it runs through but nothing is sorted

 

[Andrew Poulsom]

It worked for me. Are you sure that you want to sort by column C?

 

[psrs0810]

I think this is on my end. I think the info I am trying to sort is still a formula. Let me make a change and see if that works

 

[psrs0810]

It has to be down to formatting, the titles look the same but it is still not find them.

In order to get those items I want to sort by I am concatening the title with Subtotal. I removed the formula but it is still not finding those new titles. I even trimmed the info and it won't find it.
any suggestions?

 

[psrs0810]

Ok, still not working.

I have information from Columns A:O (originally is was to AL, bu tthose columns were deleted)

The information I am trying to sort by is in column C and it starts in cells C4.

I am not sure what is wrong and why it is not sorting