Filter Excel Pivot Table based on Cell Value

aroig07

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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,632
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"
 

Forum statistics

Threads
1,141,740
Messages
5,708,235
Members
421,553
Latest member
Geeyj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top