CODEWITHSUNDEEP
excelvba
Adolfo Rangel
Adolfo Rangel

Reputation: 15

Filter by column name all sheets macro

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

Upvotes: 0

Views: 106

Answers (1)

Gary's Student
Gary's Student

Reputation: 96763

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.

Upvotes: 1

Related Questions