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

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

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