Updating Pivot Tables via VBA

[Guest]

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

 

[Debra Dalgleish]

For the row fields, you can show all the items, then hide the ones that
don't match the selection. For example, with a Region field selection in
cell J2:

'=========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
Dim intASO As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'On Error Resume Next

Select Case Target.Address
Case Range("I2").Address
strField = "Source of Funds"
Set ws = ActiveSheet
For Each pt In ws.PivotTables
pt.ManualUpdate = True
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
pt.ManualUpdate = False
Next pt
'Call Formatting
Case Range("J2").Address
strField = "Region"
Set ws = ActiveSheet
For Each pt In ws.PivotTables
pt.ManualUpdate = True
With pt.RowFields(strField)
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
For Each pi In .PivotItems
pi.Visible = True
Next pi
For Each pi In .PivotItems
If pi.Value = Target.Value Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
.AutoSort intASO, .SourceName
End With
pt.ManualUpdate = False
Next pt
'Call Formatting
Case Else
'do nothing
End Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

'=============================