Custom Sort Order

[Steve C]

I have a spreadsheet (no headers) with project names listed in col. A and a
classification in col. B, as follows:

Project1 WUC
Project2 A
Project3 B
Project4 WUC
Project5 A
Project6 A
Project7 WUC
Project8 B

Projects are added frequently to this list by users, and my goal is a VBA
solution to sort the list in a custom order: WUC, then A, then B. To get
myself started, I recorded a macro where I created a custom list, then sorted
by that list. I noticed the recorded code makes a reference to
"OrderCustom:=7", which I realize is the desired position in MY custom list.
However, I know that not every user has the same custom lists I have, so I
how do I adapt my code to help other users sort by the same criteria?

Here's part of my recorded code:

Application.AddCustomList ListArray:=Array("WUC", "A", "B")

'Code to select range of projects

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=7, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I realize the answer may have nothing to do with creating a custom list in
the first place. Thanks for any help to get me on the right track!

 

[Steve C]

I have come up with my own solution:

Sub CreateOrder()
Dim N As Integer

Application.AddCustomList ListArray:=Array("WUC", "A", "B")

N = Application.GetCustomListNum(Array("WUC", "A", "B")) + 1
'1 added to give true position of desired order in custom list

'Code to select desired sort range

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=N, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub