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:

Sub Macro9()
' Macro9 Macro

ActiveSheet.PivotTables("ItemQtySold").PivotFields("ItemCode").PivotFilters. _
Add2 Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet. _
PivotTables("ItemQtySold").PivotFields("Average"), Value1:=3000

End Sub

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.


Views: 1792

Reply to This

Replies to This Discussion

Hi Doug.

I ran an example on my local install which worked and it kicked out the below code:

Sub Macro2()
' Macro2 Macro

ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductCodeName"). _
PivotFilters.Add2 Type:=xlValueIsGreaterThanOrEqualTo, DataField:= _
ActiveSheet.PivotTables("PivotTable1").PivotFields("GP "), Value1:=100
End Sub

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?

How about before the active sheet line where the error started, you explicitly put in the line to select the relevant worksheet, Eg: sheet("xxxx").select

What would the syntax be for doing this?

The name of the sheet is: Item Qty Sold Trend

Sheets("Item Qty Sold Trend").select

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.


The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service