CODEWITHSUNDEEP
vbaexcel
TurboCoder
TurboCoder

Reputation: 1011

VBA - Apply Filter Across Multiple Sheets if Column Header is a Match

I have a macro that renames certain headers, deletes certain columns and inserts a row on specific sheets.

I need to apply a filter across the sheets IF the header is found on the sheet.

The with wkbk1 statement has 2 for loops, the second loop I just copied from the first one and attempted to apply the filter.

Here is the code:

Sub ManipulateSheets()

    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim a As Long, w As Long
    Dim keepCols As Variant
    Dim wkbk1 As Workbook

    Set wkbk1 = Workbooks("testWorkbook.xlsm")

    'Set sheets to be used in each workbook
    Set ws2 = wkbk1.Sheets("mySheet")
    Set ws3 = wkbk1.Sheets("hisSheet")
    Set ws4 = wkbk1.Sheets("herSheet")

    keepCols = Array("Employee Number", "Status")
    filterCols = Array("Status")

    wkbk1.Activate

    ws2.Activate

    Range("A1").EntireRow.Insert

    Range("A1").Value = "Employee Number"

    ws3.Activate

    Range("A1").EntireRow.Insert

    Range("A1").Value = "Employee Number"

    ws4.Activate

    Range("A1").EntireRow.Insert

    Range("A1").Value = "Employee Number"

    For Each ws1 In wkbk1.Sheets

        ws1.Cells(1, 1).EntireRow.Replace What:="USERID", Replacement:="Employee Number", Lookat:=xlWhole
        ws1.Cells(1, 1).EntireRow.Replace What:="STATUS", Replacement:="Status", Lookat:=xlWhole
        ws1.Cells(1, 1).EntireRow.Replace What:="USER_ID", Replacement:="Employee Number", Lookat:=xlWhole
        ws1.Cells(1, 1).EntireRow.Replace What:="USER-ID", Replacement:="Employee Number", Lookat:=xlWhole
        ws1.Cells(1, 1).EntireRow.Replace What:="USER_STATUS", Replacement:="Status", Lookat:=xlWhole
        ws1.Cells(1, 1).EntireRow.Replace What:="HR_STATUS", Replacement:="Status", Lookat:=xlWhole

    Next ws1

    With wkbk1

        For w = 1 To .Worksheets.count

            With Worksheets(w)

                For a = .Columns.count To 1 Step -1

                    If UBound(Filter(keepCols, .Cells(1, a), True, vbTextCompare)) < 0 Then _
                            .Columns(a).EntireColumn.Delete

                Next a

            End With

        Next w

        For w = 1 To .Worksheets.count

            With Worksheets(w)

                For a = .Columns.count To 1 Step -1

                    If UBound(Filter(filterCols, .Cells(1, a), True, vbTextCompare)) < 0 Then _
                            .AutoFilter Field:=1, Criteria1:=("INACTIVE"), Operator:=xlFilterValues

                Next a

            End With

        Next w

    End With

End Sub

Currently, when I run this code, everything works great up until this line:

.AutoFilter Field:=1, Criteria1:=("INACTIVE"), Operator:=xlFilterValues

I get the error:

Named argument not found

Upvotes: 0

Views: 280

Answers (1)

Xabier
Xabier

Reputation: 7735

@EitelDagnin you should first use .Find to see if the header exists and then use the AutoFilter, something like:

Set StatusFound = Worksheets(w).Rows(1).Find(What:="Status", LookAt:=xlWhole)
'Above search for the Word "Status" on the first row of the Sheet

If NOT StatusFound is Nothing then
'''''If Status is found then
'''''Apply your filter

    For a = .Columns.count To 1 Step -1
        If UBound(Filter(filterCols, Worksheets(w).Cells(1, a), True, vbTextCompare)) < 0 Then _
            Worksheets(w).UsedRange.AutoFilter Field:=StatusFound.Column, Criteria1:=("INACTIVE"), Operator:=xlFilterValues
        End If
    Next a
End if

Upvotes: 2

Related Questions