Cannot get form dropdown and macro to connect to Pivot filter.

[ectiger86]

Instead of using slicers I would like to use dropdown lists to filter a Pivot table and pivot chart. Found this Macro on youtube and it was exactly what i needed. https://www.youtube.com/watch?v=S5Ix5a8GezE&list=FL_oli1FqDXvw11HOHmulHdA

Here is the code I modified from the video to operate my "CATEGORY" filter.

Sub DropDown12_Change()
On Error Resume Next
        Sheets("TEST").PivotTables("PivotTable2").PivotFields("CATEGORY").ClearAllFilters
    Sheets("TEST").PivotTables("PivotTable2").PivotFields("CATEGORY").CurrentPage = Sheets("TEST").Range("B29").Value

End Sub

for some reason the guy in the video has a cell link. I used the INDEX formula that the guy used in the video but I can only get the number of the category to change in the cell and can not get the dropdown to change the filter on the table.

any ideas?

 

[ectiger86]

https://www.youtube.com/watch?v=FW89ITnAq2Q


I ended up finding another way to accomplish what I wanted and it was even better than the first one.

Enjoy!