linerah.blogg.se - Excel slicer 2013

EXCEL SLICER 2013 CODE

For starters, you need to set each PivotTable's ManualUpdate property to TRUE so that they don't refresh ater each and every PivotItems changes. If you want the user to be able to select multiple items, things become way, way more complicated.

EXCEL SLICER 2013 CODE

There's a bit of code in there to ensure that the user can't select more than one item in the slicer at a time.īut what if you want the User to be able to select multiple items? If Target.Name = "PivotTable1 Slave" Then VArray = Array("PivotTable2 Slave", "PivotTable3 Slave") Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Here's the code that achieves this: Option Explicit and this will work even if the field you want to filter on isn't visible in any of your pivots: And then more magic happens: that single selection in those slave PageFields gets replicated in the master PivotTables thanks to those hidden Slicers we set up earlier. PageField of the PivotTable1 Slave PivotTable. PageField of those other slave PivotTables to match the. When they select an item using it, it generates a PivotTable_Update event for that PivotTable1 Slave PivotTable, which we keep an eye out for. Now this is where the clever hack comes in: We move the Slicer that is connected to the PivotTable1 Slave PivotTable into the main sheet so the user can click on it. connect each hidden Slicer to it's visible counterpart PivotTable using the Report Connections box. Connect each of those Slicers up to the actual PivotTables you had to begin with.

Again, these will be somewhere out of sight:

Make sure the 'Select Multiple Items' checkbox is deselected for each of those slave PivotTables:.

Somewhere out of sight, and put the field of interest in each of Set up a slave PivotTable for each of the master PivotTables Here's an example where I sync three different PivotTables that are on different caches. If you only want the user to select just one item at a time, you can do this very quickly by using the following trick that leverages off a quirk to do with PageFields. Set siShort = scShort.SlicerItems(siLong.Name) Set siLong = scLong.SlicerItems(siLong.Name) Set scLong = wb.SlicerCaches("Slicer_Department2")įor Each siLong In scLong.VisibleSlicerItems Set scShort = wb.SlicerCaches("Slicer_Department") Is there a more direct way of coding this or are there any potentially volatile lines in here causing Excel to fry it's brain? Private Sub Worksheet_PivotTableUpdate _ Application.EnableEvents in an attempt to speed up the macro but it's still laggy and causes Excel to become unresponsive. Basically when the value of slicer1 changes, it will change slicer2 to match slicer1 thus updating any pivot table connected to the second slicer. I finally found a code that will connect slicers with different caches on pivot table update.