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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,390
Office Version
  1. 2013
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,181,597
Messages
5,930,794
Members
436,761
Latest member
mintwaxed

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