Pivot Tables and Macros

[Niceaction]

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

 

[Jim Thomlinson]

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...

 

[DomThePom]

Not sure that Jim addressed your question......

I think what you want is to choose a data fieldfrom a validated list of data
fields and then have ypur pivot pick up your choice and change its data
field. If so, the code you need is as follows:
(right click your sheet tab and click on view code - paste the following
into the Worksheet_change event)

Dim pvt As PivotTable
Dim pvtf As PivotField
Const VALIDATED_CELL_NAME As String = "DataNames"
If Target.Address = Range(VALIDATED_CELL_NAME).Address Then
Application.ScreenUpdating = False
Set pvt = Target.Parent.PivotTables(1)
Set pvtf = pvt.DataFields(1)
If pvtf.SourceName <> Target.Value Then
pvt.PivotFields(Target.Value).Orientation = xlDataField
pvtf.Orientation = xlHidden
End If
Application.ScreenUpdating = True
End If
Set pvtf = Nothing
Set pvt = Nothing

All you need to do then is to name your validation cell (I have used
'DataNames') and then copy this name into the constant definition in the code
instead of 'DataNames'