+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    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.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

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

    Hi dluhut,

    I've run into this problem before. I think the answer was to create 3 separate range names for the same set of data. Make your pivots from these 3 different names (I made Dynamic Named Ranges for the data, all different names). Then when you refresh or change a filter it will only affect the single pivot. This is what I think happens. Give it a try and see if all slicers are reset if they come from different (named only) sets of data.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

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

    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.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

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

    It seems to me that Pivot Table manipulation is done in memory. I think it ties a chunk of memory to your pivot. If you have different names for your data range, it might create different chunks of memory for each (which are really the same data)... Now when you change the slicer on one, it might not affect the other sliced range of data. I'd need to prove my point with an example, but this is how I would try to get around what I think you are describing.

  5. #5
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

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

    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?

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

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

    Each pivot table's data source is from a different named range, and yes all named ranges span the same set of data.

    Do the process by hand first and then write the code.

  7. #7
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

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

    I tried and still didn't work.

    Attached is a sample.
    Attached Files Attached Files

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

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

    Hi,

    Find attached my old work on linked pivot tables (before slicers came out). See if the code helps with your problem. If you change a filter on one of the pivots it will affect the other pivots also. I think this is close to what you are asking.

    PivotTablesLinked.xlsm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. code that will count the number of items selected in a slicer on a pivot table
    By asmi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2017, 12:57 AM
  2. Pivot Table and Slicer Help
    By matt85webb in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-28-2016, 11:14 AM
  3. [SOLVED] Run macro to change pivot table row field when user selected a slicer item
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2016, 09:44 AM
  4. Pivot table and slicer help
    By finchfinch in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-23-2016, 07:36 AM
  5. Replies: 19
    Last Post: 04-04-2016, 02:58 PM
  6. [SOLVED] Use slicer selections on one pivot table to filter another pivot table
    By porkandbeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 12:30 AM
  7. Pivot Table (Slicer) Help
    By skate1991 in forum Excel General
    Replies: 0
    Last Post: 10-11-2012, 11:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1