Filter multiple sheets using variable cell value

Question

I have a workbook with multiple sheets.

Every sheet with data has a common column, K, which contains Client Manager names. In Sheet2 I have used a Data Validation field in C1 using a list, so creating a drop down where I can select a Client Manager. So if I select Charlie Brown, and run a macro, I would like all sheets to be filtered to only show Charlie Brown’s data.

I am an absolute VBA beginner, so I have harassed Mr Google mercilessly – the majority of suggestions involve hard-coding the filter value, rather than making it a variable cell value. The best I have found is this:

Sub apply_autofilter_across_worksheets()
Dim xWs As Worksheet
On Error Resume Next
For Each xWs In Worksheets
xWs.Range("K").AutoFilter 1, CLng(Sheets("Sheet2").Range("C1").Value)
Next
End Sub

When I run the macro:

• Positive - no error!
• Negative - nothing happens

I'm not sure what this does: xWs.Range("K") - the original script had a number after the column letter, but no matter what number I put after it, it makes no difference.

I also simply typed a Client Manager name into C1, with no impact. So clearly it's just all busted. There are 8 data worksheets, plus Sheet2. The number of columns vary sheet to sheet, but none are more than AZ.

Any help would be greatly appreciated please!

“on error resume next” hides errors. Remove that line and then re run your code. — urdearboy

BigBen BigBen · Accepted Answer · 2018-06-27T02:38:58

As mentioned in the comments, On Error Resume Next hides errors, but does not deal with them. Using Clng definitely causes an error - this would try to convert the value in C2 to type Long when you're dealing with a String. Also, you need to specifically not filter Sheet2.

How about something like this? (assumes your data begins in A1 on each Sheet.)

Edited to only autofilter visible sheets.

Sub apply_autofilter_across_worksheets()
    Dim ws As Worksheet
    Dim clientManager As String
    Dim lastCol As Long, lastRow As Long
    Dim filterRng As Range

    clientManager = Sheets("Sheet2").Range("C1").Value

    For Each ws In Worksheets
        If ws.Name <> "Sheet2" And ws.Visible Then
            With ws
                If .AutoFilterMode Then .AutoFilter.ShowAllData

                lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
                lastRow = .Cells(Rows.Count, 1).End(xlUp).Row

                Set filterRng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
                filterRng.AutoFilter 11, clientManager
            End With
        End If
    Next ws

End Sub

Filter multiple sheets using variable cell value

1 Answers