基于单元格值的 VBA 数据透视表过滤器更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42407209/
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
VBA pivot table filter change based on cell value
提问by Fitzy
I have a pivot table "IncTrend" with 2 filters. Both of the filters I have renamed "Service1" and "Service2" (They were originally both called Service when the table was created from powerpivot)
我有一个带有 2 个过滤器的数据透视表“IncTrend”。我已将两个过滤器重命名为“Service1”和“Service2”（当从 powerpivot 创建表时，它们最初都称为 Service）
In A2 of the worksheet I have a name of a service validation list with names of the items in the filter (they match exactly).
在工作表的 A2 中，我有一个服务验证列表的名称，其中包含过滤器中项目的名称（它们完全匹配）。
I am attempting to write a VBA code that will read in the "Choice" from A2, and change the pivot table filter "Service1" & "Service2" to match it.
我正在尝试编写一个 VBA 代码，该代码将从 A2 中读取“选择”，并更改数据透视表过滤器“Service1”和“Service2”以匹配它。
The premise is that I will have many different pivot tables with different data that I need to change the filter to match, but I cannot even get the one to change using VBA.
前提是我将有许多不同的数据透视表，我需要更改过滤器以匹配这些数据，但我什至无法使用 VBA 更改其中的数据透视表。
I keep getting
我不断得到
Unable to get the PivotFields property of the PivotTable class
无法获取 PivotTable 类的 PivotFields 属性
Any assistance would be greatly appreciated. Here is an SS of the Pivot table.
任何帮助将不胜感激。这是数据透视表的 SS。
My code is:
我的代码是：
Sub Filter_Change()
Dim WS As Worksheet: Set WS = ActiveWorkbook.Worksheets("Main")
Dim PT1 As PivotTable: Set PT1 = WS.PivotTables("IncTrend")
Dim PF1 As PivotField: Set PF1 = PT1.PivotFields("[Inc Open].[Service].[Service]")
Dim Choice As String: Choice = Worksheets("Main").Range("A2").Value
With PF1
.ClearAllFilters
.CurrentPage = Choice
'.PivotFilters.Add Type:=xlCaptionEquals, Value1:=Choice
End With
End Sub
回答by Shai Rado
Replace your line :
更换您的线路：
PF1.CurrentPage.Name = Choice
With:
和：
With PF1
.ClearAllFilters
.CurrentPage = Choice
End With
