dynamic pivot table selection

[mcarter]

Please bear with me, I'm sure I don't have the right lingo, but I'll give it
my best shot.

I have a pivot table with page items. I want to be able to write a macro
that will dynamically set a "page" item in the pivot table to what the user
selects from a list on a data validation cell. So if the user selects Monday
in cell A1, I want the pivot table for that page item item to change to
"Monday". I know how to write code to change pivot items for a value that is
predermined. ie I always want the page item to be Monday. But have never
made the code dynamic and interactive with a value in a cell. Thanks!

 

[Hong Quach]

Hi mcarter,

Sound like you already know how to set the PT's pagefield with VBA, and you
just want to some how make that code to run when a user change a value in
another cell. If this is the case, then there is not much work required to
solve your problem.

Let assume you have a macro called UpdatePTPageField() which will update the
PT to a predefined value. And cell A1 with a list of validation data that
the user use to select one of the choice.

Sub UpdatePTPageField()
'Code to update the PT page field to "MONDAY"
End Sub

Now You should modify the UpdatePTPageField() to accept a parameter (value
of cell A1, and use this parameter to update the PT.

Sub UpdatePTPageField(PageFieldItem As String)
' Code to update the PT page field to PageFieldItem
End Sub

Next, you need to setup the worksheet to detect changes in cells A1. To do
this, go to the code view of the worksheet and select "Worksheet_Change"
event.

Private Sub Worksheet_change(ByVal Target As Range)
Dim ws As WorkSheet
Set ws = ActiveSheet
If (Target.Row = 1 And Target.Column = 1) Then
' Code to do thing here if the Cell "A1" changes
UpdatePTPagefield(Range("A1"))
End If
End Sub

Hong Quach