Controlling AutoFilter

[FracinDean]

I reviewed 15-20 posts and tried a lot of different options, but I'm still having trouble. I'm trying to accomplish two tasks: 1) Turn off Autofilter only if it's on, and 2) Turn AutoFilter on for the specified range in a single worksheet. I only need to manipulate AutoFilter on the sheet called "Schedule" whenever it gets activated; however, the code below is where I most recently landed after trying many different things that I have been reading about on this forum. The following code is running and it will remove previous filters AND add the ones I want back when filters are there. Okay so far... The problem is when the filters are not there, the code is running, and it is selecting the range that I want to autofilter (paused the code right after the select), but the filters are not being added. I would be grateful for any help.

Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        ws.UsedRange.AutoFilter
        On Error GoTo 0
    Next ws
    
    Sheets("Schedule").Select
    With ActiveSheet
      Range("ResourceFilterRange").Select
      Selection.AutoFilter
    End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

You can use

ws.AutoFilterMode = False

To turn filters off. then to set the filters for the range you want filter, assume first column for any cell that is not blank.

 Sheets("Schedule").Range("ResourceFilterRange").AutoFilter 1, "<>"

Your code in the OP is missing the Field and criteria to filter which are both required to autofilter for a specific value, but the filters are turned on by the code. The above code eleminates the Select/Selection verbage and uses direct coding to set the filter and designate the field and criteria. Maybe this will be helpful.

 

[FracinDean]

Beautiful. Thanks JLGWhiz.

 

[JLGWhiz]

Beautiful. Thanks JLGWhiz.

You're welcome,
regards, JLG