pivot table macro with a filtering variable

[oercim]

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.

 

[isabelle]

hi,

may be:

"[CONNECTION1].[UWY].&[" & z & "]"

isabelle

Le 2014-09-11 12:42, oercim a écrit :

 

[oercim]

11 Eylül 2014 Perşembe 19:48:48 UTC+3 tarihinde isabelle yazdı:

hi,



may be:



"[CONNECTION1].[UWY].&[" & z & "]"



isabelle



Le 2014-09-11 12:42, oercim a �crit :

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 solvethe problem. Thanks a lot.

That didn't solve the problem isabelle. I can't solve this problem. There must be an a solution

 

[isabelle]

have you declared the variable x As Integer ?
Dim x As Integer

isabelle

Le 2014-09-15 13:21, oercim a écrit :