Running Excel 2010.
Have a Pivot Table that I want to use a Macro to Filter a value.
I start the recording click on the field filer - select Value Filters - Greater Than or Equal To - The Value Filter window appears - I select the field I want to use for the filter and then enter 3000 in the value box. Click OK then stop recording.
When I run the Macro I get the:
Error Box: Run-time-error '1004' Unable to get the PivotPields property of the PivotTable class.
Click Debug and shows the following code:
' Macro9 Macro
Add2 Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet. _
The name of the PivotTable is ItemQtySold
The name of the field I am filtering on is ItemCode
Average is the name of the field that contains the values I want to filter greater Than or Equal To 3,000.
Debug highlights in Yellow the whole line starting with ActiveSheet.
Any help would be greatly appreciated.
I ran an example on my local install which worked and it kicked out the below code:
' Macro2 Macro
PivotFilters.Add2 Type:=xlValueIsGreaterThanOrEqualTo, DataField:= _
ActiveSheet.PivotTables("PivotTable1").PivotFields("GP "), Value1:=100
This was on Excel 2013. I've read of cases where it appears and then disappears.
Peter - It seems to be happening on this one PivotTable. I can create a new basic PivotTable from the raw data and it works.
I want the filter to show only Items with Average Qty Sold Greater Than or Equal to 3000.
The Average Qty Sold is a Calculated field. But I get the error filtering on any of the columns.
I've attached the output. Maybe you can see what the Macro doesn't like about this particular PivotTable.
I tested using a few different fields and got the same problem.
Well it's good to know it wasn't something I did.
How can I get the Macros to work?
What would the syntax be for doing this?
The name of the sheet is: Item Qty Sold Trend
I read a long time ago that selecting the sheet first can solve a number of macro related errors. I've also read now that people have received that error from incorrect formatting of cells and trailing spaces and things like that.