Filter by column name all sheets macro

Question

I'm trying to filter the same column called "Location" from all sheets in the Workbook. The filter will be the same, in this case, selecting: "ABM", "AKH" & "ACH". The column is not on the same position in the all the sheets.

I've tried combining some codes I've found on google and that worked separately. But when combined, it only works for one sheet. I'm thinking it has something to do with the "For" that stops.

Sub AllSheetsAutofilter()
    Dim p As Integer, q As Integer
    p = Worksheets.Count
    Dim i As Integer, rngData As Range

    For q = 1 To p
        With Worksheets(q)
            Set rngData = Range("A1")
            i = Application.WorksheetFunction.Match("Location", Range("A1:AZ1"), 0)
            rngData.AutoFilter Field:=i, Criteria1:=Array("ABM", "AC8", "AKH", "ACH", "AC4"), Operator:=xlFilterValues
        End With
    Next q
End Sub

`Set rngData = .Range("A1")`. You need the period in front of `Range`, otherwise you are not referencing `Worksheets(q)` in the `With` block. — BigBen, Jul 09 '19 at 14:51
Wow! it did worked! thanks a lot BigBen. One more thing, I do get the error "Unable to get the Match property of the WorksheetFunction class". I think it is because some of the sheets doesn't have the data searched. How do you stop this error from appearing? Thanks again! — Adolfo Rangel, Jul 09 '19 at 15:00
See [this question](https://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor) — BigBen, Jul 09 '19 at 15:06

score 1 · Answer 1 · answered Jul 09 '19 at 15:06

1

You were close. This works for me:

Sub AllSheetsAutofilter()

    Dim p As Long, q As Long
    p = Worksheets.Count
    Dim i As Long, rngData As Range

    For q = 1 To p
        With Worksheets(q)
            Set rngData = .Cells
            i = Application.WorksheetFunction.Match("Location", .Range("A1:AZ1"), 0)
            rngData.AutoFilter Field:=i, Criteria1:=Array("ABM", "AC8", "AKH", "ACH", "AC4"), Operator:=xlFilterValues
        End With
    Next q

End Sub

Note the use of Long and the use of qualified variables.

answered Jul 09 '19 at 15:06

Gary's Student

95,722
10
59
99

Hi Gary, thanks for the response. When running your code I do get the error "Unable to get the Match property of the WorksheetFunction class". Why does this happens? – Adolfo Rangel Jul 09 '19 at 16:15
@AdolfoRangel That error will occur if one or more worksheets do not have a cell in the top row containing **Location** – Gary's Student Jul 09 '19 at 16:36
Declare i as variant not as long so as to handle the error. And before the `rngData.AutoFilter` add `If Not IsError(i) Then` – YasserKhalil Jul 11 '19 at 02:57

Filter by column name all sheets macro

1 Answers1