Pivot Tables and Macros
Here is some code for you. I am assuming you have only 1 table in your sheet
and that you drop down is in G1. Also you will need to change the page field
name from "This" to whatever you have...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pvt As PivotTable
Dim pvi As PivotField
If Target.Address < "$G$1" Then Exit Sub 'Validation list in G1
Set pvt = Target.Parent.PivotTables(1) 'Change the table?
Set pvi = pvt.PageFields.Item("This") 'Change Page Filed Name
pvi.CurrentPage = Target.Value
End Sub
This code will go directly in the sheet. Right click the sheet tab select
view code...
--
HTH...
Jim Thomlinson
"Niceaction" wrote:
Hi,
I'm a newbie at pivot tables, but have been asked by my boss to create one
for a bunch of end users who are even less computer literate than him!
My pivot table is set up and ready to go, but I have 4 possible "value"
fields that a user might want to see summarised within the pivot table.
What I would like is to have a drop down box with the 4 possible values in,
and everytime a user selects one - say "Actual Balance Outstanding" then it
uses that data field within the pivot table.
I can set up the drop down box using a validated list, but I am at a loss as
to how I can then automate the field selection.
Any suggestions would be really appreciated!
Thanks
|