pivot table macro with a filtering variable
 

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.

pivot table macro with a filtering variable
 

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

pivot table macro with a filtering variable
 

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



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

pivot table macro with a filtering variable
 

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

isabelle

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

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