Sorting Range Works - the Second time

[JPARKHURST]

OK. I have a fairly simple worksheet which I have two buyers sorting to see what parts they need. I then need it sorted a different way, so there are 3 ways this sheet needs to be searched.

The sorts for Judy & Sara work. The sort for Jon/Default works...the second time. The first time it looks like it does an OK job - resets (not removes!) filters, but it is not actually sorting them, just releasing the filters. the second time it works as intended, but of course it's not needing to reset filters. So I'm wondering - is there some way that it is not resetting the filters and then sorting?

Here is my sorting function:

Public Sub ofSorts(sBuyer42 As String)
    Dim ws42 As Worksheet
    Dim r42 As Range
    Dim lr42 As Long
    
    Application.ScreenUpdating = False
    Set ws42 = Sheets("Shortages")
    lr42 = ws42.Cells(Rows.Count, "A").End(xlUp).Row
    Set r42 = ws42.Range("A2:J" & lr42)
    
    Select Case sBuyer42
        Case "Judy"
            r42.Sort key1:=Range("I2:I" & lr42), order1:=xlAscending, _
            key2:=Range("C2:C" & lr42), order2:=xlAscending
            r42.AutoFilter Field:=9, Criteria1:="Judy", VisibleDropDown:=True
        Case "Sara"
            r42.Sort key1:=Range("I2:I" & lr42), order1:=xlAscending, _
            key2:=Range("C2:C" & lr42), order2:=xlAscending
            r42.AutoFilter Field:=9, Criteria1:="Sara", VisibleDropDown:=True
        Case "Default"
            On Error Resume Next
            Call ResetFilters(r42)
            r42.Sort key1:=Range("b2:b" & lr42), order1:=xlAscending, _
            key2:=Range("g2:g" & lr42), order2:=xlAscending
        Case Else
            MsgBox ("I'm guessing you're a buyer, cause you made a mistake...")
        End Select
        
        Application.ScreenUpdating = True
End Sub

Here is my reset sub

Public Sub ResetFilters()
    On Error Resume Next
    If ActiveSheet.FilterMode = True Then
        ActiveSheet.ShowAllData
    End If
End Sub

Any suggestions?

TIA,

Jon

 

[rlv01]

The sorts for Judy & Sara work. The sort for Jon/Default works...the second time.

How do Judy, Sara, and Jon pertain to the code you posted? Better to clear the filters before sorting. Also with an "On Error" statement ahead of the reset code, you don't know what might be happening. I'm making the big assumption that row 1 (A:J) contains the headers for your sort range, so if that's not the case then you'll have to adjust the code

Sub ofSorts(sBuyer42 As String)
    Dim ws42 As Worksheet
    Dim r42 As Range
    Dim lr42 As Long
    Dim CriteriaStr As String

    Application.ScreenUpdating = False
    Set ws42 = Sheets("Shortages")

    With ws42
        lr42 = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set r42 = .Range("A1:J" & lr42)               'sort range including the header row

        CriteriaStr = ""
        Select Case sBuyer42
        Case "Test1"
            CriteriaStr = "Judy"
        Case "Test2"
            CriteriaStr = "Sara"
        Case "Default"
            ResetFilters ws42
            r42.Sort key1:=.Range("B1"), order1:=xlAscending, key2:=.Range("G1"), order2:=xlAscending, Header:=xlYes
        Case Else
            MsgBox "I'm guessing you're a buyer, cause you made a mistake...", vbExclamation + vbOK
        End Select

        If CriteriaStr <> "" Then
            ResetFilters ws42
            r42.Sort key1:=.Range("I1"), order1:=xlAscending, key2:=.Range("C1"), order2:=xlAscending, Header:=xlYes
            r42.Columns.AutoFilter Field:=9, Criteria1:=CriteriaStr, VisibleDropDown:=True
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Sub ResetFilters(ByRef WS As Worksheet)
    If WS.AutoFilterMode Then
        WS.ShowAllData
    End If
End Sub