Custom sort a table using multiple criteria

[Diving_Dan]

Hi again,

I have a table of data containing staff information that I need to custom sort however i'm struggling to find anything online to do what I need.

The Table is called Table1 and it is on sheet "A Team". I would like to custom sort column "B" which has the header "Department" in the order: "Warehouse", "Back Office", "Hospitality", "Customer Services". I then want to sort column "D" ascending which has the header "Surname" and then by column "E" which has the header "First Name".

As always thanks in advance.

 

[MoshiM]

Edited to add an equal sign.

Sub CustomSort_Diving_Dan()

Dim TBO As ListObject, C_B As Long, WS As Worksheet

Application.AddCustomList Array("Warehouse", "Back Office", "Hospitality", "Customer Services")

C_B = Application.CustomListCount

Set WS = ThisWorkbook.Worksheets("A Team")
Set TBO = WS.ListObjects("Table1")

With TBO
   
    With .Sort
       
        With .SortFields
           
            .Add Key:=WS.Cells(1, 2), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=C_B
            .Add Key:=WS.Cells(1, 4), SortOn:=xlSortOnValues, Order:=xlAscending
            .Add Key:=WS.Cells(1, 5), SortOn:=xlSortOnValues, Order:=xlAscending
           
        End With
       
        .MatchCase= False
        .Header = xlYes
        .Apply
       
    End With

End With

Application.DeleteCustomList C_B

End Sub

 

[Diving_Dan]

That works perfectly thanks.

If I wanted to custom sort column G first using a different criteria how would I create and add in that custom list?

 

[Diving_Dan]

It's ok i've worked it out. Thanks again