Filter Excel Pivot Table based on Cell Value

[aroig07]

Hi,

I am creating a Glossary for a table with a lot of descriptions of certain activities. I want to be able to type in a cell a value and click the comand button to filter automatically instead of in the Pivot filter. I tried with this code but keep getting an error on the highlighted code line. I have done multiple Watch to try and identify why it is not working and have not succeded. I currectly have one Pivot Table by the name of PivotTable1, the cell where I am writing is in cell E4 and the category in the Pivot that is wanted to filter is named End Result.


Sub TestPivot()

Dim dt As String
Dim pf As PivotField
Dim pi As PivotItem

dt = Sheets("Glossary").Range("E4").Value

Sheets("Glossary").PivotTables("PivotTable1").PivotFields("End Results").ClearAllFilters

Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("End_Result")
For Each pi In pf.PivotItems
If pi.Name = dt Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

End Sub


Help is greatly apprreaciated )

 

[DanteAmor]

Sheets("Glossary").PivotTables("PivotTable1").PivotFields("End Results").ClearAllFilters 'with or without "s"
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("End_Result") 'with or without "_"

Check the field name: "End Result"