Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Filtering - Macro | Excel Programming | |||
pivot table variable range within a macro | Excel Programming | |||
Macro on filtering pivot table (pivot fields) = debug | Excel Programming | |||
variable last cell in pivot table macro | Excel Programming | |||
selecting variable rows in a macro that creates a pivot table | Excel Programming |