Filter Excel Pivot Table based on Cell Value

aroig07

New Member
Joined
Feb 26, 2019
Messages
36
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 :))
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,304
Members
410,545
Latest member
Upsindustrial20
Top