Is there a better way to sync pivot tables?

[twilson]

Hi- I have two pivot tables (PivotTable1 and PivotTable2) that pull from the same data. The pivot tables have one shared field. I would like PivotTable2 to sync with (mimic) PivotTable1 based on the values filtered in the shared field (in the pic the shared field is the "Person" field). All of this would be simple enough--BUT i want to filter PivotTable1 with a slicer that does not reference PivotTable2. (Due to the way the data MUST be organized I cannot simply copy the source fields down in the data tab.)

To repeat for clarity, lets just say I am trying to use VBA or something there about to make PiviotTable2 sync with PivotTable1 (which is filtered by a non-shared field "Hair' in my example file.)

(I tried to post a pic here, but it didn't work)

I'm not much of a VBA coder, but piecing together some things, I've been able to get a workaround where I have cell references (in Column K) that refer to the Person field in PivotTable1. Then on a pivottableupdate event, it runs a macro (PT) that loops through the values in the cell range (Column K) and filters PivotTable2 accordingly. So this works, but it seems silly and slow (the actual spreadsheet I am running this on is considerably larger than my example file.) But as I am not much of a programmer it was the best I could come up with?? Surely there is a better way? I'd like to get rid of the cell references (Column K) and it seems like the loop idea is inefficient? Alright you geniuses, whatcha got?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
On Error Resume Next
 If Target.Name = "PivotTable1" Then
   Call PT
  ' MsgBox ("Hi")
End If
Application.EnableEvents = True
End Sub

'And This Part Goes in the Module

Sub PT()

Application.EnableEvents = False

ActiveSheet.PivotTables("PivotTable2").PivotFields("Person").ClearAllFilters

Dim PT As PivotTable
Dim PI As PivotItem
Set PT = Sheets("Tables").PivotTables("PivotTable2")
''    PT.PivotFields("Person").CurrentPage = ("All")  This line was causing an error?!?
For Each PI In PT.PivotFields("Person").PivotItems
   PI.Visible = WorksheetFunction.CountIf(Sheets("Tables").Range("K12:K31"), PI.Name) > 0
Next PI
Set PT = Nothing
Application.EnableEvents = True
End Sub

Thanks for your help!