+ Reply to Thread
Results 1 to 7 of 7

Run macro when pivot slicer filter is cleared

  1. #1
    Registered User
    Join Date
    04-19-2012
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    41

    Run macro when pivot slicer filter is cleared

    i'm hoping this is an easy one, but wracking my brains trying to use create a simple fix.

    I am trying to remove an auto-filter (across A6:O6) on a small table (not an actual table) of data, when the 'clear filter' button is clicked on a slicer. I already have the slicer filter the table so it hides rows based on the slicer selection by way of a macro as its not actually connected to the slicer. The table is just static and populated values from pivots on another sheet. The only issue is when the slicer filter is cleared the table remains filtered and so if possible I would like the clearing of the pivot slicer remove the autofilter from A6:O6.

    I could just add a macro button to 'show all' or 'reset' that removes the autofilter but it would be nice to tie it in with clearing the pivot slicer.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Run macro when pivot slicer filter is cleared

    Hi,

    What code are you using to filter the table currently?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    04-19-2012
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Run macro when pivot slicer filter is cleared

    Hi there,

    Thanks, using the code below, cell 'SSX2' is the value of a one column pivot table that is linked to the slicer, so when the user selects an option from the slicer, a couple of charts (also connected to pivot tables/slicer) change. The rows in range A6:N20 are hidden except the one that is selected via the slicer.


    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Run macro when pivot slicer filter is cleared

    What happens to that pivot table when the slicer is cleared? Does it expand, or does SSX2 contain "All", or something else?

  5. #5
    Registered User
    Join Date
    04-19-2012
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Run macro when pivot slicer filter is cleared

    The pivot table (with 1 column) that resets when the slicer is cleared shows all, with a blank value in cell SSX2. The blank value needs to exist however as there is data attached to it where the field is blank. So the table that is getting filtered as a result of the slicer change just gets filtered to the row with a blank in 'Field 2'. This is where I would like it to just turn off the autofilter and show all in range A6:N20.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Run macro when pivot slicer filter is cleared

    From that description, I think you could simply test that cell then
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-19-2012
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Run macro when pivot slicer filter is cleared

    Works perfectly thank you so much!
    Overall its not a great way of doing things, but its sort of an extra request that I've been trying to crowbar in. But its doing what it should and nothing is breaking which is always nice
    Thanks again for your help.

+ 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. Filter pivot with slicer for each column
    By tsakta13ole in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-11-2017, 06:07 AM
  2. Pivot Table with Slicer - Add Macro???
    By ronarosario in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2017, 11:30 AM
  3. Filter a 2nd pivot with the inverse of a slicer
    By spikey_plant in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-27-2015, 12:55 PM
  4. Replies: 2
    Last Post: 07-30-2014, 05:50 AM
  5. Updating a pivot filter with another pivot updated by a Slicer
    By Jabba69 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2014, 04:08 PM
  6. [SOLVED] Link Pivot Tables, Filter with One Slicer
    By Pierce Quality in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-23-2013, 11:19 AM
  7. [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

Tags for this Thread

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