Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Pivot Tables
Hi all, first post so please be gentle! :) I'm currently putting together a VBA Macro to allow less Excel minded people to use a complex pivot table. I am able to get the macro to run as I want it to, except I cannot find the right way to allow the user to enter variables on a front sheet that will then be picked up by the macro and then the right pivot table function selected. I currently have: Range("A8").Select With ActiveSheet.PivotTables("PivotTable2").CubeFields( "[DATE]") ..Orientation = xlPageField ..Position = 1 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("[DATE]").CurrentPageName = _ "[DATE].[All DATE].[2006].[January]" Columns("A:A").ColumnWidth = 18.71 With ActiveSheet.PivotTables("PivotTable2").CubeFields( "[ACCESS_CAMPAIGN]") ..Orientation = xlPageField ..Position = 2 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("[ACCESS_CAMPAIGN]"). _ CurrentPageName = "[ACCESS_CAMPAIGN].[All ACCESS_CAMPAIGN].[0844]" etc etc etc However, where it selects [0844] above, I would like this to select whatever value is shown in cell C3 of the worksheet called 'Inputs' instead. Is this possible? Thanks in advance Stu -- Stu-Pid ------------------------------------------------------------------------ Stu-Pid's Profile: http://www.excelforum.com/member.php...o&userid=30823 View this thread: http://www.excelforum.com/showthread...hreadid=504869 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Pivot Tables
Possibly
s = worksheets("Inputs").Range("C3").Value CurrentPageName = _ "[ACCESS_CAMPAIGN].[All ACCESS_CAMPAIGN].[" _ & s & "]" -- Regards, Tom Ogilvy "Stu-Pid" wrote in message ... Hi all, first post so please be gentle! :) I'm currently putting together a VBA Macro to allow less Excel minded people to use a complex pivot table. I am able to get the macro to run as I want it to, except I cannot find the right way to allow the user to enter variables on a front sheet that will then be picked up by the macro and then the right pivot table function selected. I currently have: Range("A8").Select With ActiveSheet.PivotTables("PivotTable2").CubeFields( "[DATE]") Orientation = xlPageField Position = 1 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("[DATE]").CurrentPageName = _ "[DATE].[All DATE].[2006].[January]" Columns("A:A").ColumnWidth = 18.71 With ActiveSheet.PivotTables("PivotTable2").CubeFields( "[ACCESS_CAMPAIGN]") Orientation = xlPageField Position = 2 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("[ACCESS_CAMPAIGN]"). _ CurrentPageName = "[ACCESS_CAMPAIGN].[All ACCESS_CAMPAIGN].[0844]" etc etc etc However, where it selects [0844] above, I would like this to select whatever value is shown in cell C3 of the worksheet called 'Inputs' instead. Is this possible? Thanks in advance Stu -- Stu-Pid ------------------------------------------------------------------------ Stu-Pid's Profile: http://www.excelforum.com/member.php...o&userid=30823 View this thread: http://www.excelforum.com/showthread...hreadid=504869 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Pivot Tables
Thanks for the reply. It seems to work perfectly, many thanks! : -- Stu-Pi ----------------------------------------------------------------------- Stu-Pid's Profile: http://www.excelforum.com/member.php...fo&userid=3082 View this thread: http://www.excelforum.com/showthread.php?threadid=50486 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) | |||
Pivot tables-controlling user interaction with pivot tables | Excel Programming |