Custom List Sort with Run-time Error

[Lindsay0385]

Hello - I hoping someone can help me with this.

I created a macro using the Macro Recorder to sort a column on my spreadsheet by a custom list.

CUSTOM LIST:
PROJECT CANCELLED​

STAGE 1 - PI
STAGE 2 - RD
STAGE 3 - W
STAGE 4 - LV
STAGE 5 - LD
PROJECT COMPLETE

VBA CODE

    ActiveWorkbook.Worksheets("Test").AutoFilter.Sort. _
        SortFields.Add2 Key:=Range("B4:B999"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, CustomOrder:= _
        "PROJECT CANCELLED,STAGE 1 - PI,STAGE 2 - RD,STAGE 3 - W,STAGE 4 - LV,STAGE 5 - LD, PROJECT COMPLETE" _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Test").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The macro works perfectly on my PC. But when I send the file to my coworker, she gets a "Run-time error '438': Object doesn't support this property or method". On her PC, I tried sorting the column by the custom list manually and I realized that the custom list I made doesn't show up when she opens the workbook.

So I did some googling and added a line to the macro to create the same custom list before my initial code:

Application.AddCustomList ListArray:=Array("PROJECT CANCELLED", "STAGE 1 - PI", "STAGE 2 - RD", "STAGE 3 - W", "STAGE 4 - LV", "STAGE 5 - LD", "PROJECT COMPLETE")

I was hoping that would fix it, but she's still getting the run-time error, even though it works perfectly for me. I'm so confused.

Any help anyone can provide would be appreciated.

Thanks!
Lindsay

 

[Joe4]

Are macros/VBA enabled on her computer?
Are you both using the same version of Excel, or is she using an older one?

In the VB Editor, go to the Tools menu, and select References and note all the different libraries you have selected.
Have her do the same and compare the lists. Is she missing any selections?

 

[Fluff]

Try changing the Add2 here

SortFields.Add2 Key:=Range("B4:B999"), SortOn:=xlSortOnValues, Order:= _

to just Add

 

[Lindsay0385]

Are macros/VBA enabled on her computer?
Are you both using the same version of Excel, or is she using an older one?

In the VB Editor, go to the Tools menu, and select References and note all the different libraries you have selected.
Have her do the same and compare the lists. Is she missing any selections?

We both are on Microsoft Office 16 and have the same selections checked in the References:
-Visual Basic for Applications
-Microsoft Excel 16.0 Object Library
-OLE Automation
-Microsoft Office 16.0 Object Library

 

[Lindsay0385]

Try changing the Add2 here

SortFields.[COLOR=#ff0000]Add2 [/COLOR]Key:=Range("B4:B999"), SortOn:=xlSortOnValues, Order:= _

to just Add

THANK YOU! So simple! I wonder why it worked on my PC with no problems.

 

[Fluff]

If you're both running the same version of Xl, I would have thought it would work for both of you.
Add2 appeared in Xl 2016, although I've no idea what it does, as opposed to Add.