Refresh pivot table on slicer change

[Alexis]

Hi all,

I have a dashboard which is controlled by slicers, but one of the charts is linked to a separate source which is affected but not controlled by the slicer.

All I'm looking to do is to refresh one pivot table automatically each time a slicer is changed.

Any ideas?

Thanks,
Alexis

 

[Chihiro]

AFAIK - There is no way to reference Slicer change event directly in VBA.

Workaround:
Assuming your slicer updates at least on pivot table...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
End Sub

Reference
https://msdn.microsoft.com/en-us/library/office/ff822105.aspx?f=255&MSPPError=-2147217396

So you can refresh the pivot table not updated by Slicer by coding macro to fire after one of the associated pivot updates.

 

[Luke M]

You could also assign a macro to the slicer. Not as nice as a "slicer_event" would be, as it would be the same as clicking a button shape and would run on EVERY click, but it might work.

 

[jeffreyweir]

I'm in the process of finishing some code that effectively generates PivotField_Update and Slicer_Update events and then intelligently syncs any Pivots/Slicers that you want it to.

This code is the combination of a whole bunch of routines of mine, and is based on some posts I've previously done over at http://dailydoseofexcel.com/archives/2014/07/10/what-caused-that-pivottableupdate-episode-iv/ and http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

The code is VERY long and complicated. I'm still doing some troubleshooting, but can post it here sometime in the next week, as I need some beta testers anyhow.

@Luke M :I'd never thought about simply assigning macros to slicers to capture direct clicks. I'm going to have to incorporate that into my routine.

 

[jeffreyweir]

So you can refresh the pivot table not updated by Slicer by coding macro to fire after one of the associated pivot updates.

The problem is, the PivotTable_Update event gets triggered by lots of things that have nothing to do with filtering or slicers. Which is why I'm writing my code...to identify exactly what caused an update, and if caused by a filter or slicer action, to identify which field(s) are affected. Check out that Daily Dose of Excel post (in fact, go back to the previous 3 posts linked to in that 4th post for a thorough treatment of my approach)

 

[jeffreyweir]

By the way, my code doesn't need a VBA expert to use. In fact, i'm turning it into an add-in that I can sell alongside my book.

 

[Chihiro]

The problem is, the PivotTable_Update event gets triggered by lots of things that have nothing to do with filtering or slicers. ...

You are quite right. Thanks for the link. It's quite a lot to take in, but good read.

 

[Alexis]

Hi all,

Thanks very much for your suggestions, Chihiro's Worksheet_PivotTableUpdate worked great.

Jeff - thanks for the links, I'll check those out.

Alexis