I have a problem. I will be very glad for any help.

I have a pilot table where tthe data source is obtained from an out source named "CONNECTION1". The below code is obtained by macro saving. It works fine.


Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").PivotFields ( _
"[CONNECTION1].[UWY].[UWY]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ( _
"[CONNECTION1].[UWY].[UWY]").CurrentPageName = _
"[CONNECTION1].[UWY].&[2012]"
End Sub

However, I want to define a "z"" variable which will be declared for filtering "year". The updated code is:

Sub Macro1()
z=2012
ActiveSheet.PivotTables("PivotTable1").PivotFields ( _
"[CONNECTION1].[UWY].[UWY]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ( _
"[CONNECTION1].[UWY].[UWY]").CurrentPageName = _
"[CONNECTION1].[UWY].&[z]"
End Sub

However, I get an error. I tried many combinatios but I could not solve the problem. Thanks a lot.