Excel VBA：清除数据透视表中的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17885167/
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
Excel VBA: clear items in pivot table
提问by Ben
I am new to VBA... I am trying to write a macro that will clear all the selections within a pivot table filter named "Product Family" and select only the item whose name is contained in cell "B33". I am referencing the pivot table in one sheet "sheet8" and trying to change a graph on "Dashboard".
我是 VBA 新手...我正在尝试编写一个宏，该宏将清除名为“产品系列”的数据透视表过滤器中的所有选择，并仅选择名称包含在单元格“B33”中的项目。我在一张纸“sheet8”中引用数据透视表并尝试更改“仪表板”上的图表。
Here is the code...
这是代码...
Sub thisisalsotemp()
'
' thisisalsotemp Macro
'
'
Sheets("Dashboard").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Sheet8.PivotTables("capbylp").PivotFields("Product Family").PivotFields.ClearAllFilters
With Sheet8.PivotTables("capbylp").PivotFields("Product Family")
.PivotItems(Range("B33")).Visible = True
End With
End Sub
The error is in the following line: Sheet8.PivotTables("capbylp").PivotFields("Product Family").PivotFields.ClearAllFilters
错误在以下行： Sheet8.PivotTables("capbylp").PivotFields("Product Family").PivotFields.ClearAllFilters
The error message is: Object doesn't support this property or method
错误信息是：对象不支持此属性或方法
@SeanCheshire: Thanks for the help. I feel this is much closer to what I want. However, I couldnt get it to work. I played around with it a little bit and am closer. here is what i have...
@SeanCheshire：感谢您的帮助。我觉得这更接近我想要的。但是，我无法让它工作。我玩了一会儿，离它更近了。这是我所拥有的...
Sub thisisalsotemp2()
Sheets("Dashboard").Select
Sheet8.PivotTables("capbylp").PivotFields("Product Family") = Range("B33")
End Sub
Error 1004 reads: unable to set the pivotfields property of the pivottable class
错误 1004 读取：无法设置数据透视表类的数据透视字段属性
in the line: Sheet8.PivotTables("capbylp").PivotFields("Product Family") = Range("B33")
在线： Sheet8.PivotTables("capbylp").PivotFields("Product Family") = Range("B33")
采纳答案by SeanC
you need to set CurrentPage(and you shouldn't need to clear it first).
Using what is shown in your code, I would have something like:
您需要设置CurrentPage（并且您不需要先清除它）。
使用您的代码中显示的内容，我会有类似的内容：
Sheet8.PivotTables("capbylp").PivotFields("Product Family"). _
PivotFields("MyPivotField").CurrentPage = Range("B33").Value
(broken into 2 lines for readability)
（为了便于阅读，分成 2 行）
回答by Aaron West
This is slightly related; I wanted to clear multiple-selections whenever the user makes them. Apparently, setting the VisibleItemsList can do that.
这有点相关；我想在用户进行多选时清除它们。显然，设置 VisibleItemsList 可以做到这一点。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xPF As PivotField
Dim nms As Variant
nms = Array("Calculation", _
"Rate Type", _
"xx Hierarchy")
Set xPT = Application.ActiveSheet.PivotTables(1)
For Each xPF In xPT.PageFields
For Each nm In nms
If xPF.Name Like "*" & nm & "*" Then
If UBound(xPF.VisibleItemsList) > 1 Then
xPF.VisibleItemsList = Array("")
End If
End If
Next nm
Next xPF
End Sub
