How to run a macro when a Filter is applied on Par

Question

I have seen how to call a macro when a filter is applied on the pivot table here: How to run a macro automatically when the selections of pivot tables are changed

But I have a different requirement, I have 5 Pivot Fields and each filter change has a different function to perform. So, I'm wondering if it is possible to assign a different macro for each of the Pivot Fields?

I know that I can use worksheet_Change function and then use range as a parameter to check whether a filter is applied in that range or not. But the problem here is, I'll be moving the Pivot Fields to Column labels and Row Labels within the macro which will call other macros subsequently.

I don't want that to happen. That is the reason I'm searching for a way to detect Pivot Filter change on ONE PIVOT FIELD

Answer 1

Yes you can, but it's incredibly difficult, because the PivotTable_Update event doesn't tell you which particular field got updated. I've found a way that largely works on process of elimination, and uses the force in the event that all else fails. See my series of posts that cover this at the following links:

• http://dailydoseofexcel.com/archives/2014/07/07/what-caused-that-pivottableupdate-part-one/
• http://dailydoseofexcel.com/archives/2014/07/08/what-caused-that-pivottableupdate-part-two/
• http://dailydoseofexcel.com/archives/2014/07/09/what-caused-that-pivottableupdate-part-tres/
• http://dailydoseofexcel.com/archives/2014/07/10/what-caused-that-pivottableupdate-episode-iv/

[Edit] A simpler alternative if you're using Excel 2010 or later is to set up a slicer for each PivotField, and make a new PivotTable for each of the fields in the original PivotTable, with nothing but that field in it. Then connect it to the original PivotTable via a Slicer. When anyone filters either the original PivotTable or clicks on the Slicer, it will generate a PivotTable_Update event for both the master PivotTable, and the smaller PivotTable that is connected to that particular field. So you just check the name of the PivotTables that the Update event was raised for, and then trigger the appropriate macro accordingly. Here's how that looks conceptually:

...and here's the code that conditionally calls a macro depending on what field or slicer the user clicked on:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Select Case Target.Name
Case "PivotTable2": 'put name of macro you want to trigger here
Case "PivotTable3": 'put name of macro you want to trigger here
End Select
End Sub

Put that code in the ThisWorkbook code module.