Filter all sheets by filtering one sheet

[SamT]

If you place the cursor next to or inside a keyword and press F1, the help page for that key word will display.

Wow, what a mess of old redundant spaghetti code.

• Old: Note that the variable byteColumn is Typed as a Byte. A Byte is limited to 256, the column limit for Excel < 2007.
• Redundant: "boolFilterOn = False: i = 0: byteColumn = 0" is setting the uninitialized variables to their uninitialized values. There are more examples

First see: http://www.ozgrid.com/VBA/autofilter-vba.htm
Then: https://msdn.microsoft.com/en-us/lib.../ff194617.aspx

You will see that you really only need one Procedure to accomplish what you want.

1. First apply some of the code in the first link to see if there are any filters on the active sheet, IF NOT then exit the sub.
2. Next, the first Procedure in the second link will save the filters, then loop thru the remaining sheets with the second procedure.

Let us know if you need more help.

Code in the Links:

With ActiveSheet
         If .AutoFilterMode = True And .FilterMode = True Then
             MsgBox "They are visible and in use"
         ElseIf .AutoFilterMode = True Then
             MsgBox "They are visible but not in use"
         Else
             MsgBox "They are not visible or in use"
         End If
    End With

Dim w As Worksheet 
Dim filterArray() 
Dim currentFiltRange As String 
 
'Sub ChangeFilters() 
Sub SaveFilters()
 
Set w = Worksheets("Crew") 
With w.AutoFilter 
 currentFiltRange = .Range.Address 
 With .Filters 
 ReDim filterArray(1 To .Count, 1 To 3) 
 For f = 1 To .Count 
 With .Item(f) 
 If .On Then 
 filterArray(f, 1) = .Criteria1 
 If .Operator Then 
 filterArray(f, 2) = .Operator 
 filterArray(f, 3) = .Criteria2 
 End If 
 End If 
 End With 
 Next 
 End With 
End With 
End Sub

Sub RestoreFilters() 
Set w = Worksheets("Crew") 
w.AutoFilterMode = False 
For col = 1 To UBound(filterArray(), 1) 
 If Not IsEmpty(filterArray(col, 1)) Then 
 If filterArray(col, 2) Then 
 w.Range(currentFiltRange).AutoFilter field:=col, _ 
 Criteria1:=filterArray(col, 1), _ 
 Operator:=filterArray(col, 2), _ 
 Criteria2:=filterArray(col, 3) 
 Else 
 w.Range(currentFiltRange).AutoFilter field:=col, _ 
 Criteria1:=filterArray(col, 1) 
 End If 
 End If 
Next 
End Sub