Filter based on a cell's value.

[Doug24]

Is it possible to create a macro to filter data based on a cell value?

I have 34 worksheets and I need to filter them each day based on a name in cell K16. The data is
below from A17 to AM47 and needs to be filtered in column K (field 11). Also if the name doesn't
appear in column K is it possible to just default to the "Select All" filter option? The names aren't
constant and change daily. I'm doing the filtering manually now and would like to automate
it if possible.

Thanks in advance for any help,
Doug

 

[Osvaldo Palmeiro]

Hi. Try:

Sub FilterTest()
 On Error Resume Next
 ActiveSheet.ShowAllData
 If [K16] <> "" And Application.CountIf([K17:K47], [K16]) > 0 Then [A17:AM47].AutoFilter 11, [K16]
End Sub

 

[Doug24]

Thank you Osvaldo - that works perfectly!

 

[mart37]

    Dim FoundRow As Variant
    ActiveSheet.AutoFilterMode = False
    FoundRow = Application.Match(Range("K16"), Range("K18:K47"), 0)
    If IsError(FoundRow) Then
        ActiveSheet.Range("$A$17:$AM$47").AutoFilter
    Else
        ActiveSheet.Range("$A$17:$AM$47").AutoFilter Field:=11, Criteria1:=Range("K16")
    End If

 

[Doug24]

Thank you mart37 - that does exactly what I need.
Now I have a couple of options to work with!

Thank you for your help.
Doug