Linking Pivot tables - Multiple Page Fields

**mettekr** · 02-16-2010, 04:07 PM

Hi all,

I have two pivot tables on one sheet and I want the page fields on the second to change when I change the first pivot table. I found the below code and have applied it to one of the three page fields I have, but can't seem to duplicate it for the other page fields:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, strField As String
strField = "SLS_MDL"
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each ptTable In ActiveSheet.PivotTables
    If ptTable <> Target Then
        ptTable.PageFields(strField).CurrentPage = Target.PageFields(strField).CurrentPage.Value
    End If
Next ptTable
ExitPoint:
Application.EnableEvents = True
End Sub

You can see in the code that my page field is "SLS_MDL". I also have the fields "origin" and "destination" I'd like to have change in both places.

Thanks!

**seckert** · 02-17-2010, 02:54 PM

I have several sheets that do this - you should be able to just add each new element as follows:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, strField As String, strField2 as String
strField = "SLS_MDL"
strField2 = "origin"
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each ptTable In ActiveSheet.PivotTables
    If ptTable <> Target Then
        ptTable.PageFields(strField).CurrentPage = Target.PageFields(strField).CurrentPage.Value
       ptTable.PageFields(strField2).CurrentPage = Target.PageFields
(strField2).CurrentPage.Value 
 End if
Next ptTable
ExitPoint:
Application.EnableEvents = True
End Sub

Good Luck

SAE

**mettekr** · 02-19-2010, 03:23 PM

Thanks for the help, but something must not be right. I copy/pasted your code and still only the SLS_MDL field will change for both tables. I even switched the order so origin was strField and still just the SLS_MDL one did it as strField2...that doesn't really seem to make sense.

Any thoughts? I can attach a copy of the file if that would help

Thanks

**DonkeyOte** · 02-19-2010, 03:28 PM

Posting a sample is always a good idea... though not really any different than prior post the below is a slight variation in terms of processing multiple page fields of interest:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, vFields As Variant, lngField As Long
vFields = Array("SLS_MDL","origin","destination")
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each ptTable In ActiveSheet.PivotTables
    If ptTable <> Target Then
        For lngField = LBound(vFields) To UBound(vFields) Step 1
            ptTable.PageFields(vFields(lngField)).CurrentPage = Target.PageFields(vFields(lngField)).CurrentPage.Value
        Next lngField
    End If
Next ptTable
ExitPoint:
Application.EnableEvents = True
End Sub

EDIT: worth adding that the above is not designed for use wherein multi item selection is active on the Page Field (ie hidden items pre XL2007)

It's always worth outlining version... 2007 and beyond are quite different in many respects to their predecessors - esp. in regard to PTs

**mettekr** · 02-19-2010, 04:43 PM

Still no dice...just the SLS_MDL changes. I'm not sure if I fell into the disclaimer you listed. This workbook was made in Excel 2003...Here's a sample of the file.

Thanks