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.