VBA and Pivot Tables

[Stu-Pid]

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

 

[Tom Ogilvy]

Possibly

s = worksheets("Inputs").Range("C3").Value
CurrentPageName = _
"[ACCESS_CAMPAIGN].[All ACCESS_CAMPAIGN].[" _
& s & "]"

 

[Stu-Pid]

Thanks for the reply.

It seems to work perfectly, many thanks! :