VBA - Loop Through Multiple Worksheets and Apply Filter

Question

I need to apply the same filter to all the worksheets in the same workbook.

All the sheets have headers in the first row, but the header name that the filter is supposed to be applied to is not always in the same column, i.e, worksheet one the header in question is in column H, in worksheet 2 it's in column A, in worksheet 3 it's in column L and so on...

Also, some of the headers will not be an exact match for the criteria - i.e, some will have "STATUS" as the header, some will be "prefix_Status", others "CurrentStatus" and so on.. So I need to use the Instr funciton (Unless there's some better option) but I cannot seem to figure out where or how to use it..

Here is the code I have so far:

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.count

         ' Begin the loop.
         For I = 1 To WS_Count

            Dim count As Integer, rngData As Range
            Set rngData = Range("A1").CurrentRegion

            count = Application.WorksheetFunction.Match("STATUS", Range("A1:AZ1"), 0)

            rngData.autofilter Field:=count, Criteria1:="INACTIVE"

         Next I

End Sub

This code applies the filter to only ONE sheet.

Answer 1

You are referring always to the ActiveSheet, whenever you do not specify the worksheet explicitly in your code. Thus, in the Range() you have to refer to the worksheet like this:

From:

Set rngData = Range("A1").CurrentRegion
count = Application.WorksheetFunction.Match("STATUS", Range("A1:AZ1"), 0)

Change to:

With Worksheets(I)
    Set rngData = .Range("A1").CurrentRegion
    count = Application.WorksheetFunction.Match("STATUS", .Range("A1:AZ1"), 0)
End With

The dots in the code between With Worksheets(I) - End With are what makes the difference:

---

Concerning the Application.WorksheetFunction.Match, it only matches cells which contain exactly the word "STATUS". If there is something else like a space before or a sign after, then something like this is a good idea:

count = Application.Match("*STATUS*", Worksheets(1).Range("A1:AZ1"), 0)

Then a check is still needed. Like this:

If Not IsError(count) Then
    rngData.autofilter Field:=count, Criteria1:="INACTIVE"
End If

---

Concerning the second part of the question, use * around the value in the Match function:

Public Sub TestMe()    
    Range("E1") = "5teSt34"
    Debug.Print Application.WorksheetFunction.Match("*Test*", Range("A1:H1"), 0)    
End Sub

Will always return 5.

Answer 2

Basically what you need to do is reference the sheet in your code as you loop through, which you are not doing - you are only referring to the active sheet by not including any references.

With Match you can use wildcards.

Sub WorksheetLoop()

Dim WS_Count As Long
Dim I As Long

WS_Count = ActiveWorkbook.Worksheets.count

Dim count As Variant, rngData As Range
For I = 1 To WS_Count
   Set rngData = Worksheets(I).Range("A1").CurrentRegion
   count = Application.Match("STATUS", Worksheets(I).Range("A1:AZ1"), 0)
   If IsNumeric(count) Then rngData.AutoFilter Field:=count, Criteria1:="INACTIVE"
Next I

End Sub