Pivot Multi Page Diff Change

[xlMS2SF]

I need help adapting VBA code that I found on Contextures.com

When the end user chooses the two page fields in ptMain, I need it to
automatically enter it in pt2, pt3 and pt4. pt2 is on the same page as
ptMain. All four pivot tables use the same page filter.

Here is code, I just don't know how to adapt.
=================


Code Section:

Option Explicit
Dim mvPivotPageValue1 As Variant
Dim mvPivotPageValue2 As Variant
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
''based on code by Robert Rosenberg posted 2000/01/11
''A module level variable (mvPivotPageValue) keeps
''track of the last selection from the Page Field.
Dim wsOther As Worksheet
Dim pt As PivotTable
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable
Dim pi As PivotItem
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strField4 As String
strField1 = "Item"
strField2 = "Region"
strField3 = "Product"
strField4 = "District"
Set wsOther = Sheets("Other Pivots")
Set pt = Target
Set pt1 = wsOther.PivotTables("PT1")
Set pt2 = wsOther.PivotTables("PT2")
Set pt3 = wsOther.PivotTables("PT3")
On Error Resume Next
If LCase(pt.PivotFields(strField1).CurrentPage) <> LCase(mvPivotPageValue1)
Then
'The PageField1 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue1 = pt.PivotFields(strField1).CurrentPage

With pt1.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt2.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt3.PageFields(strField3)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Application.EnableEvents = True
End If
If LCase(pt.PivotFields(strField2).CurrentPage) <> LCase(mvPivotPageValue2)
Then
'The PageField2 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue2 = pt.PivotFields(strField2).CurrentPage
With pt1.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt2.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt3.PageFields(strField4)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Application.EnableEvents = True
End If
End Sub


+++++++++++++

 

[Debra Dalgleish]

You could use the following code that will match all the page fields,
based on an update to any one of the pivot tables on the same sheet:

'======================================
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pi As PivotItem
Dim pf As PivotField

On Error Resume Next
Set ws = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
For Each pt In ws.PivotTables
If pt.Name <> ptMain.Name Then
For Each pf In pt.PageFields
If pf.Name = pfMain.Name Then
If pfMain.CurrentPage = "(All)" Then
pf.CurrentPage = "(All)"
Exit For
Else
For Each pi In pf.PivotItems
If pi.Name = pfMain.CurrentPage Then
pf.CurrentPage = pi.Name
Exit For
End If
Next pi
End If
End If
Next pf
End If
Next pt
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
'===================================

 

[xlMS2SF]

Dear Debra,

Thank You. Thank you for taking the time and your for your response. My
pivot tables use an external database. Your code worked when the data source
is from an excel spreadsheet. However, I could not get it to work for the
external data source.

Michael