Stand out VBA Tip – How You Can Reset All Pivot Table Slicers In The Mouse Click

      Comments Off on Stand out VBA Tip – How You Can Reset All Pivot Table Slicers In The Mouse Click

I’ve lately been focusing on a spreadsheet solution based greatly on Pivot Tables and a large amount of Slicers to allow a simple of drilll lower in to the data. If you’ve been using any form of Stand out from Microsoft Excel 2010 onwards, then you definitely most likely have previously used slicers to navigate round the data inside your Pivot Tables.

Slicers provide buttons that you could click to filter Pivot Table data, but additionally towards the quick filtering, slicers also indicate the present filtering selection, that makes it clear to see what is proven inside a filtered Pivot Table report.

Which means this drill lower appeared to become great, with my spreadsheet solution, only one issue that did show up was the amount of clicks it required to ‘Start Over’ or reset all the Slicers to show all the data. (There have been a number of slicers around the work sheet- like 4). Whether this really is considered a great deal is most likely dependent on personal opinion… and just how experienced you’re in governing the work sheet while using Pivot Table Slicers however the feed away from this audience was there were a lot of clicking options.

The task could be that the slicers were must be there to drill lower but required too lengthy to reset.

So, I made the decision to utilize a tiny bit of code to allow one button to reset all the Slicers with a single click. So this is actually the small bit of code in my macro that we then mounted on a control button around the work sheet.

Sub ClearMySlicers()

Dim Slcr As SlicerCache

For Every Slcr In ActiveWorkbook.SlicerCaches

Slcr.ClearManualFilter

Next

Finish Sub

Open your Stand out work sheet striking F11 to spread out the Visual Fundamental Editor.

Within the left hands drop lower box double click the work book and paste the code within the window. (This can produce a macro that runs whenever we select it. This macro is going to be known as ClearMySlicers).

x from the developer window so we can check up on the macro

Developer Tab Code Group – Macros a single article the Macro Dialog box.

We’re able to hit Run came from here each time you want to reset the Pivot Slicers, but it’s simpler to simply supply the work book users having a button to click.

Choose the Developer Tab – Controls Group – Insert – Form Controls

Choose the Push Button Icon

Click where you need to put the button in your work sheet

The Assign Macro Dialog Box can look

Choose the Macro we simply produced striking OK

Right click the button to alter the written text in Edit Text if you want to- within my example I altered it to Begin Again!

And that’s it, all we have to do is test drive it. Works wonderful. Everybody is satisfied.

Are you into a career where you have to handle managerial reports? Then the pivot table training is just what you need to be a part of. With skills coming from industry experts, you get to achieve perfection after what you learn.