VBA to set PowerPivot filter

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
I'd like to set a filter - based on a measure - in a PowerPivot table using VBA. The macro recorder gave this code (without the handy With...End With usage of course):

With ActiveSheet.PivotTables("pvtCartItemAdd").PivotFields("[All New HFHS Carts].[Was Added].[Was Added]")
.ClearAllFilters
.CurrentPage = "[All New HFHS Carts].[Was Added].&[Added]"
End With

With ActiveSheet.PivotTables("pvtCartItemRemove").PivotFields("[All Old HFHS Carts].[Was Removed].[Was Removed]")
.ClearAllFilters
.CurrentPage = "[All Old HFHS Carts].[Was Removed].&[Removed]"
End With

Re-running the code works for the .ClearAllFilters line but gives error 1004 on the .CurrentPage. I've tried various tricks with and without the ampersand, square brackets and such, but no dice. From what I can see online this is the right syntax, but I'm wondering if the fact that the filter is based on a measure rather than a field is causing VBA to throw the error.
 

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.

Forum statistics

Threads
1,225,726
Messages
6,186,669
Members
453,368
Latest member
xxtanka

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
Back
Top