Setting values in a Pivot Table Filter Drop down the same as another Pivot Table Filter Drop list

[TrishMitra]

I have two Pivot tables Pivot1 and Pivot2 on Sheet1. Both have the same data source so same data.

In the Pivot table field list, in Filters there is one filter called Year. This is a list of years (2001,2002,...2017). Both tables have Year as a filter. On the dropdown filter list Select Multiple Items is checked so the user may select more than one year if he wishes.

On PivotTableUpdate I would like the following: when the user selects one or more years in Pivot1 Year filter, then in Pivot 2 Year filter those values should automatically be selected.

I managed to do this when the user selects 1 year only by
getting the value of Worksheets("Sheet1").PivotTables("Pivot1").PivotFields("Year").CurrentPage
and setting the values of Worksheets("Sheet1").PivotTables("Pivot2").PivotFields("Year").CurrentPage.

However after much trying am not sure what to do if the user selects multiple values of Year in Pivot1. Would be grateful for some help. Thanks. I haven't done any programming for 12 years now and am a little rusty

 

[TrishMitra]

Hi

I figured how to do this one. I made a Slicer which is connected to both the reports.

Cheers