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.
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.