Automatically change pivot table filter when another one changes

Question

Ok so I have two pivot tables and I want them to always show the same filters. The problem is, the filters are not labeled the same. For example, filter "A" in the first table corresponds to filter "1" in the second table. I have it working fine with a lookup if just one filter is selected, the problem is when multiple filters are selected I don't know how to handle it. Any ideas would be greatly appreciated.

Also, I know it would be easiest to just change the filters so they are labeled the same, but I don't have access to the data so that is not an option

Hi - which version of Excel do you have? because if you're on 2010 you could use a 'slicer' and connect it to both pivot tables — Oliver Lockett

Bmo Bmo · Accepted Answer · 2013-07-01T13:28:26

I'm using the Worksheet_PivotTableUpdate event for mine. You'll get Target as the calling table.

 Dim chgPt as PivotTable
 Set chgPt = Target

 'You'll need to catch the change for either table.
 If chgPt.Name = "table1" Then 'Change table2
   pt2.PivotFields("nameOfFilter").CurrentPage = chgPt.PivotFields("nameOfFilter").CurrentPage 

 Else 'Change table1
  pt1.PivotFields("nameOfFilter").CurrentPage = chgPt.PivotFields("nameOfFilter").CurrentPage

 End If

Automatically change pivot table filter when another one changes

1 Answers