excelvbafilter

Filter multiple sheets using variable cell value


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!


Solution

  • 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