Custom Sorting in VBA

[eli_m]

Hi,

I have the code below but I can't get this part work:

        ThisWorkbook.Sheets("IPS Cases").Range("B2").Sort Key1:=ThisWorkbook.Sheets("IPS Cases").Range("B3"), _
        Order1:=xlAscending, Header:=xlYes, _
        Key2:=ThisWorkbook.Sheets("IPS Cases").Range("F3"), Order2:=xlAscending, Header:=xlYes, _
        Key3:=ThisWorkbook.Sheets("IPS Cases").Range("H3"), Order2:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom

Basically, I want to sort by Column B first, then Column F then Column H.
B = Date
F = Name
H = Work Site

I always want to have it sorted by date but if the dates are the same I want it to sort by their name AFTER date then if the names are the same then Work Site but only after B and F.

Full Code:

Option Explicit
Private Sub Workbook_Open()

    If ActiveSheet.Name = Sheet1.Name Then
    'Start Timeout Timer
        checktime = False
        Call checktimer
    'Check Timeout timer
        checktime = True
        Lastchange = Now()
     End If
   
   
  Dim cel As Range
 
  Application.ScreenUpdating = False
  ThisWorkbook.RefreshAll
  With Sheets("IPS Cases")
    On Error Resume Next
        ThisWorkbook.Sheets("IPS Cases").Range("B2").Sort Key1:=ThisWorkbook.Sheets("IPS Cases").Range("B3"), _
        Order1:=xlAscending, Header:=xlYes, _
        Key2:=ThisWorkbook.Sheets("IPS Cases").Range("F3"), Order2:=xlAscending, Header:=xlYes, _
        Key3:=ThisWorkbook.Sheets("IPS Cases").Range("H3"), Order2:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    On Error GoTo 0
    
    For Each cel In .Range("A2:AR200") 'Ignore ALL Errors
      With cel
        .Errors(6).Ignore = True 'Lock Error
        .Errors(7).Ignore = True 'Unprotected Forumla Error
        .Errors(8).Ignore = True 'Data Validation Error
        .Errors(9).Ignore = True 'Inconsistent Error
        .Errors(10).Ignore = True 'Inconsistent Calculated Column Formula Error
      End With
      
    Next cel
  End With
  Application.ScreenUpdating = True
End Sub

Thanks in advance

 

[Micron]

Shouldn't you be using Order3 with Key3? I have not done this in vba but I got that notion from here

Range.Sort method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[eli_m]

Shouldn't you be using Order3 with Key3? I have not done this in vba but I got that notion from here

Range.Sort method (Excel)

Office VBA reference topic

learn.microsoft.com

Good pick up! This made me a look a little harder and find that I actually needed to do H before F for it to work for my needs. I also changed the order to 3.

 

[Micron]

Nice to know I can still help when I have no experience with something!
Thanks for the recognition.