在特定数据透视字段上应用筛选器时如何运行宏 [英] How to run a macro when a Filter is applied on Particular Pivot Field
问题描述
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
但是我有不同的要求，我有5个数据透视字段"，并且每个过滤器更改都有不同的功能要执行.因此，我想知道是否可以为每个数据透视字段分配一个不同的宏?
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?
我知道我可以使用worksheet_Change函数，然后使用range作为参数来检查是否在该范围内应用了过滤器.但是这里的问题是，我将把数据透视字段"移到宏内的列"标签和行"标签上，这些宏随后将调用其他宏.
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.
我不希望那样发生.这就是我寻找一种方法来检测一个PIVOT字段上的数据透视滤镜更改的原因
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
推荐答案
是的，但是这非常困难，因为PivotTable_Update事件不会告诉您哪个特定字段已更新.我找到了一种在消除过程中发挥很大作用的方法，并在所有其他方法都失败的情况下使用了这种力量.请通过以下链接查看我的系列文章，内容涉及这一点:
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/
- 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/
如果您使用的是Excel 2010或更高版本，一个更简单的选择是为每个PivotField设置一个切片器，并为原始PivotTable中的每个字段创建一个新的PivotTable，仅保留该字段.然后通过切片器将其连接到原始数据透视表.当任何人筛选原始数据透视表或单击切片器"时，它将为主数据透视表和连接到该特定字段的较小数据透视表生成一个PivotTable_Update事件.因此，您只需检查引发Update事件的数据透视表的名称，然后相应地触发相应的宏.这是概念上的样子:
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
将该代码放入ThisWorkbook代码模块中.
Put that code in the ThisWorkbook code module.
这篇关于在特定数据透视字段上应用筛选器时如何运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
