Filter Table using VBA Excel

Question

I have two worksheets. In worksheets("Dashboard") I have a dropdown list based on a table in worksheets("yearly View"). I am trying to filter this table based on the value in the drop down list in worksheets("Dashboard").

The code I have created is failing.

The drop down list in worksheets("Dashboard") is location in Range("AL1"). The column to be filtered is on worksheets("Yearly View").Range("P3").

Please see my code below:

Sub Filter_Table()
     Worksheets("Yearly View").Range("P3").Select
     Worksheets("Yearly View").Range("$P$3:$AR$24").AutoFilter Field:=1, Criteria1:= _
     Worksheets("Dashboard").Range("AL1")
     ActiveSheet.Range("$P$3:$AR$24").AutoFilter Field:=1
End Sub

Is this dropdown you mention from Data Validation or a Form listbox or even an ActiveX listbox? — Xabier
Remove the last line of code. You basically filter than unfilter again with that last line. Also, the first line can be removed as well. it's erroneous. — Scott Holtzman

Xabier Xabier · Accepted Answer · 2017-12-13T17:37:49

If you put the code below under the Worksheet_Change on the Sheet "worksheets("Dashboard")" then the value of the listbox will be used to filter the Field 3 in this case on the Sheet "Yearly View":

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AL$1" Then
Worksheets("Yearly View").ListObjects("YourTableName").Range.AutoFilter Field:=3, Criteria1:=Target.Value 'change the name of your table here. and the field value to the column number to be filtered on your table
End If
End Sub

Filter Table using VBA Excel

1 Answers