I am using a pivot table that is populated through an OLAP connection.

The pivot table is filtered in the report filter (page range) section on Store Number and Chart Section. I need Only the store number field to loop through each store number.

Manually recording a macro changing the store number from "All" to the first store results in:

Sub ChangeStoreNumber()

' ChangeStoreNumber Macro

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Organization].[Company Number - Store Number].[Company Number - Store Number]" _
).ClearAllFilters

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Organization].[Company Number - Store Number].[Company Number - Store Number]" _
).CurrentPageName = _
"[Organization].[Company Number - Store Number].&[0005 00001]"

End Sub


I would like to have a button that when pressed will loop through all of the store numbers, printing a report generated from the pivot in between. I am able to do this successfully with a normal pivot table, but with the OLAP pivot it will not work. Also - please not the store numbers appear to be a string...all starting with 0005 and then a space, followed by a 5 digit store number.

Any assistance in this would be greatly appreciated!

Thank You!