vba 更改数据透视表过滤器的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25449729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro To Change Pivot Table Filters
提问by ZAX
My goal is to change the filter of 3 pivot tables, all with the same field with the click of a button attached to a macro.
我的目标是通过单击附加到宏的按钮来更改 3 个数据透视表的过滤器，所有数据透视表都具有相同的字段。
Here is my code:
这是我的代码：
Dim pickedDate As String
Dim shift As String
pickedDate = Worksheets("Report").Range("C1").Value
shift = Worksheets("Report").Range("C2").Value
Worksheets("Report").PivotTables("PivotTable2").PivotCache.SourceData = Worksheets("Fullness").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
Worksheets("Report").PivotTables("PivotTable3").PivotCache.SourceData = Worksheets("Backed").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
Worksheets("Report").PivotTables("PivotTable4").PivotCache.SourceData = Worksheets("Hoist").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
For Each PT In ActiveSheet.PivotTables
PT.PivotFields("Date").CurrentPage = CDate(pickedDate)
PT.PivotFields("Shift").CurrentPage = shift
Next
However when I get to my loop for the pivot tables, I get the error on PT.PivotFields("Date").CurrentPage = CDate(pickedDate)
:
但是，当我进入数据透视表的循环时，出现以下错误PT.PivotFields("Date").CurrentPage = CDate(pickedDate)
：
Run time error 1004
Run time error 1004
Application Defined or Object Defined Error
Application Defined or Object Defined Error
I've verfied the source data for the tables is updated correctly.
我已验证表的源数据已正确更新。
I've tried not using the loop, and doing it manually like
我试过不使用循环，而是像
Worksheets("Report").PivotTables("PivotTable4").PivotFields("Date").CurrentPage = CDate(pickedDate)
Worksheets("Report").PivotTables("PivotTable4").PivotFields("Date").CurrentPage = CDate(pickedDate)
for each table, and it only works for one of the tables, my PivotTable3.
对于每张桌子，它只适用于其中一张桌子，我的PivotTable3.
Even more curiously, when I comment out my code to change the date, and use either way (the loop or the manual change for the 3 tables) for the shift change it works perfectly with no error.
更奇怪的是，当我注释掉我的代码来更改日期，并使用任一方式（循环或手动更改 3 个表）进行轮班更改时，它可以完美运行，没有错误。
I've verfied the name of my pivot tables, that's not the issue. The name of the field I'm trying to change appears correct as well- it works for one of the tables and I've copied and pasted that cell "Date" into the others even.
我已经验证了我的数据透视表的名称，这不是问题。我试图更改的字段名称似乎也正确 - 它适用于其中一个表，我什至已将该单元格“日期”复制并粘贴到其他表中。
What am I missing?
我错过了什么？
回答by ZAX
I'm not completely sure why this works now, but all I did to change my code was add into the loop a clear filter command for the "Date" field.
我不完全确定为什么这现在有效，但我所做的改变我的代码就是在循环中添加一个“日期”字段的清除过滤器命令。
So now my code has this loop instead for making the date and shift change for all the pivot tables based on a cell value I enter manually, just once
所以现在我的代码有这个循环，而不是根据我手动输入的单元格值对所有数据透视表进行日期和班次更改，只需一次
For Each PT In ActiveSheet.PivotTables
PT.PivotFields("Date").ClearAllFilters
PT.PivotFields("Date").CurrentPage = CDate(pickedDate)
PT.PivotFields("Shift").CurrentPage = shift
Next
Again, I'm not sure why I didn't have to do this for the shift filter, but now it works. I suggest trying that first if anyone encounters this issue
同样，我不确定为什么我不必为移位过滤器执行此操作，但现在它可以工作了。如果有人遇到此问题，我建议先尝试
