Hi,

I currently have code that updates page fields (i.e. page filters, not pivot
items) for pivot tables. I realize that I can't use this code to update pivot
items (non-page filters).

Cell I2 (on the same page as the pivot tables) has a drop-down list that
allows the user to choose an item for the page filters for three adjacent
pivot tables on one worksheet. But cells J2, K2, and L2 have drop-down lists
for pivot fields and thus I can't use the code below to adjust pivot items.
How do I adjust the code below to allow the user to not only adjust the page
filter (in cell I2), but also pivot fields in drop-down lists in cells J2, K2
and L2? Below is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'On Error Resume Next
If Target.Address = Range("I2").Address Then
strField = "Source of Funds"
Set ws = ActiveSheet
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
..CurrentPage = pi.Value 'Target.Value
Exit For
Else
..CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Call Formatting
End If


Many thanks!
Kent.


-
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557