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:

Code:

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:

Code:

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:

Code:

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

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

forgot the file...

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

Sorry for delay - "out of hours" for me now I'm afraid.

Regards your file... first off you have the latest code in the wrong sheet object (you're still running the original code against the Pivot)

Regardless, given you're hiding items in the Page Field you will need to adopt a more sophisticated approach ... ie SLS_MDL won't update to reflect changes (the others should)

Unfortunately pre XL2007 you can't iterate the items within the Page Field and test the visibility status... in reality you need to change the orientation (temporarily) of the field from Page to Row, iterate each item within the Row field (and set visibility) before subsequently moving field back to Page field... in short a "faff".

In the attached link I had put together some code - but not really tested a great deal and so I'm sure it will fail under certain circumstances

http://www.excelforum.com/2190338-post12.html

I will look at this tomorrow applying the above principles...

the good thing here is that the PTs have the same data source so there shouldn't be issues of items in one PT field not appearing in the same field in the other PT.