Auto Filter based on cell value

[badaflash]

Hi! I have an Excel spreadsheet with numerous columns and using Auto Filter. One of the columns contains people's names. If I select All or a certain name to filter, everything works well, but what I was hoping is to have a cell where I can have a drop down of showing ALL, plus everyone's names and then the Auto Filter would filter based on that selection. The reason I'm looking at doing this is that I have 5 tabs that need to be auto filtered by name, so if I can pick one cell that contains the name, then that name can be filtered on the 5 tabs automatically. At the present time, I have to go to each tab and use the auto filter for the name column to achieve the same thing - this will avoid me missing one by accident. More information:

B1 contains title: Name
C1 contains the drop down for the list of names (and ALL)
M9 shows the title Name with the drop down for auto filter
M10:M200 shows all the names
All data is contained within A9:AR200

Thank you!

 

[kevin9999]

I'm a little unclear as to your actual sheet layout, but that aside, here's a demonstration of how applying an AutoFilter on one (any) sheet - will apply the same filter on all other sheets in the workbook. For demo purposes I put the names in column A, the autofilter selection cell being A1. The code relies on a simple macro in the ThisWorkbook code module that detects a calculation in any sheet in the workbook. In this case, I've put the following formula in cell D1 on each sheet:

=COUNTA(A:A)

And the following code in the ThisWorkbook module:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Filter_All_Sheets
End Sub

With the following code in a standard module:

Option Explicit
Sub Filter_All_Sheets()
    Application.EnableEvents = False
    Dim dict As Object, lastRow As Long, c As Range, i As Long, ws As Worksheet
    Set dict = CreateObject("Scripting.dictionary")
    With ActiveSheet
        i = .Index
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For Each c In .Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible)
            dict(c.Text) = 1
        Next
    End With
    
    For Each ws In Sheets
        If ws.Index <> i Then ws.Range("A1").AutoFilter 1, Array(dict.keys), xlFilterValues
    Next ws
    Application.EnableEvents = True
End Sub

Obviously the cell references will need to be adjusted in your specific case. Here's the link to the demo file: Filter All Sheets.xlsm

Let me know how you go, and I can look at this again tomorrow my time zone.