Excel VBA Tip - How To Reset All Pivot Desk Slicers At The Click on Of A Button

 I've not too long ago been engaged on a spreadsheet answer primarily based very a lot on Pivot Tables and a great deal of Slicers to allow a simple of drilll down into the info. If in case you have been utilizing any model of Excel from Microsoft Excel 2010 onwards, then you definitely in all probability have already used slicers to navigate across the information in your Pivot Tables.

Slicers present buttons that you could click on to filter Pivot Desk information, however along with the fast filtering, slicers additionally point out the present filtering choice, which makes it straightforward to grasp what precisely is proven in a filtered Pivot Desk report.

So this drill down gave the impression to be all nicely and good, with my spreadsheet answer, however one challenge that did come up was the variety of clicks it took to 'Begin Over' or reset the entire Slicers again to indicate the entire information. (There have been fairly a couple of slicers on the work sheet- like 4). Whether or not that is deemed lots might be a matter of private opinion... and the way versed you might be in manipulating the work sheet utilizing the Pivot Desk Slicers however the feed again from this viewers was that there have been too many clicking choices.

The problem was that the slicers have been wanted to be there to drill down however took too lengthy to reset.

So, I made a decision to make use of a teeny piece of code to allow one button to reset the entire Slicers with one click on. So right here is the small piece of code for my macro which I then hooked up to a button on the work sheet.

Sub ClearMySlicers()

Dim Slcr As SlicerCache

For Every Slcr In ActiveWorkbook.SlicerCaches

Slcr.ClearManualFilter

Subsequent

Finish Sub

• Open up your Excel work sheet and hit F11 to open the Visible Fundamental Editor.
• Within the left hand drop down field double click on on the work e-book and paste the code within the window. (This may create a macro that runs after we choose it. This macro will likely be referred to as ClearMySlicers).
• x out of the developer window and we are able to verify on the macro
• Developer Tab - Code Group - Macros to convey up the Macro Dialog field.

We may hit Run from right here each time we need to reset the Pivot Slicers, however it's extra easy to only present the work e-book customers with a button to click on.

• Choose the Developer Tab - Controls Group - Insert - Type Controls
• Choose the Push Button Icon
• Click on the place you need to place the button in your work sheet
• The Assign Macro Dialog Field will seem
• Choose the Macro we simply created and hit OK
• Proper click on on the button to vary the textual content in Edit Textual content for those who want to- in my instance I modified it to Begin Over!

And that's it, all we have to do is take a look at it. Works like a dream. Everyone seems to be pleased.