Need help with changing multiple page fields.

[smonczka]

I have found some code on the web that will allow me to link pivot
table menus together. This way if I have three tables all based on the
same info and say change the date in one, the date changes in all of
them. I have tested out the code and it works well but... when I pull
data from an outside source the code fails because it can not find the
data fields. Is it possible to link related pivot table fields using
an outside data source like SQL?

The code I am using is listed below and this is the link from where I
got the code from. It's a great site.

Change Multiple Page Fields -- Change either page field in main pivot
table, and same selection is made in related pivot tables page fields.
Excel 2002 and later versions
http://www.contextures.com/PivotMultiPagesChange.zip 17 kb 21-Oct-05



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 pf1 As PivotField
Dim pf2 As PivotField
Dim strField1 As String
Dim strField2 As String

strField1 = "Item"
strField2 = "Region"

Set wsOther = Sheets("Other Pivots")
Set pt = Target
Set pt1 = wsOther.PivotTables(1)
Set pt2 = wsOther.PivotTables(2)
Set pf1 = pt.PivotFields(strField1)
Set pf2 = pt.PivotFields(strField2)

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
pt1.PageFields(strField1).CurrentPage = mvPivotPageValue1
pt2.PageFields(strField1).CurrentPage = mvPivotPageValue1
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
pt1.PageFields(strField2).CurrentPage = mvPivotPageValue2
pt2.PageFields(strField2).CurrentPage = mvPivotPageValue2
Application.EnableEvents = True
End If

End Sub

Thanks,
Steve Monczka