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!
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