ClearManualFilter on all Pivot Table EXCEPT the Pivot Table of the selected slicer

Hello,

Given that I have multiple pivot table and each of the pivot table had it's slicers (no relationship). My question is, how can I clear the filters on the pivot tables that are not of the selected slicer?

For example:

I have 3 pivot tables and 3 slicers (again not related):
pvtBuilding - slicer_building
pvtEntities - slicer_entity
pvtCity - slicer_city

Assume that if user were to select an item(s) in the slicer_building, the other 2 pivot tables (pvtCity and pvtEntities) will need to ClearManualFilter, and remain the item(s) that user had selected on slicer_building.

I tried to code it, but it's giving me a loop.

Below is the simplified version of the code that I've made

Please Login or Register  to view this content.

Initially, when I don't have Application.EnableEvents, my macro would be able to kicks in, whenever I select a value from either of the slicers. But it'll just loop through the msgbox. But when I introduced the Application.EnableEvents, the macro didn't kick in.

I don't get it. How could creating 3 different pivot tables (from the same data set), just by giving a different name range would work?

After all, what I'm facing here is the PivotTableUpdate where when one selects an item(s) from one of the slicer, the other 2 slicers will then need to ClearManualFilter, where it'll then fire off the code on the other 2 slicers, creating a loop.

But if your method works, I'd definitely like to know more on how to handle this.

Ok...so let me get this straight.

Assume that my data is from A1:C10. So what you're saying is that I create 3 different name range for the same data set (A1:C10), say nrCity, nrEntity and nrBuilding.

I then use this name range and create a pivot table.

And in VBA under Worksheet_PivotTableUpdate(ByVal Target As PivotTable), I'd be able clear the filters on the other pivot table and it won't create a loop?

I tried and still didn't work.

Attached is a sample.