vba 在数据透视表页面字段中选择多个项目时如何获取值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22983390/
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
How to get the values when multiple items are selected in PivotTable PageField?
提问by Punith GP
I am rying to ge the values of PivotField in PivotTable(1).Pivotfields, then I am trying to get the the filter value to string(str- Declared as string), It is working fine, but when I select multiple items in filter, "(All)"value is passed to str.
我正在尝试获取数据透视表（1）中数据透视字段的值。数据透视字段，然后我试图将过滤器值转换为字符串（str-声明为字符串），它工作正常，但是当我在过滤器中选择多个项目时,"(All)"值传递给 str。
How to pass the value of selected items(more than one) in pivot items.
如何在数据透视项中传递选定项（多个）的值。
For Each PF In PT1.PivotFields
If PF.Orientation = xlPageField Then
str = PF.CurrentPage
PT2.PivotFields(PF.Name).CurrentPage = str
Below is the snap of values I need to pass to str.
下面是我需要传递给 str 的值的快照。
Any help will be greately appreciated.
任何帮助将不胜感激。
回答by lowak
Try code below. First it checks if you have enabled MultiplePageItems in your PivotTable, if true, it check every item if it's selected (visible). You save those values in variable or do anything else.
试试下面的代码。首先，它检查您是否在数据透视表中启用了 MultiplePageItems，如果为 true，它会检查每个项目是否被选中（可见）。您将这些值保存在变量中或执行其他任何操作。
If ActiveSheet.PivotTables("table").PivotFields("field").EnableMultiplePageItems = True Then
For i = 1 To ActiveSheet.PivotTables("table").PivotFields("field").PivotItems.Count
If ActiveSheet.PivotTables("table").PivotFields("field").PivotItems(i).Visible = True Then 'if selected then
'your code
End If
Next i
End If
回答by Virgilio
ActiveSheet.PivotTables("PivotTable1").PivotFields("field").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("field").EnableMultiplePageItems = True
For i = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems.Count
If ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems(i).Visible = True Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems(i).Visible = False
If ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems("item name 1 ").Visible = False Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems(i).Visible = True
End If
If ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems("item name 2 ").Visible = False Then
ActiveSheet.PivotTables("PivotTable1").PivotFields(field").PivotItems(i).Visible = True
End If
this if you repeat 3, 4, 5, etc…….
End If
Next i
回答by L42
Try this:
尝试这个：
Sub marine()
Dim PT1 As PivotTable, PT2 As PivotTable
Set PT1 = Sheet1.PivotTables("PivotTable1")
Set PT2 = Sheet1.PivotTables("PivotTable2")
Dim PF As PivotField, PI As PivotItem, myfilter
For Each PF In PT1.PageFields '~~> directly work on PageFields
If PF.EnableMultiplePageItems = True Then '~~> check if multi-items
For Each PI In PF.PivotItems
If PI.Visible = True Then
If IsEmpty(myfilter) Then
myfilter = Array(PI.Caption)
Else
ReDim Preserve myfilter(UBound(myfilter) + 1)
myfilter(UBound(myfilter)) = PI.Caption
End If
End If
Next
Else
myfilter = PF.CurrentPage
End If
If IsArray(myfilter) Then
PT2.PivotFields(PF.Name).EnableMultiplePageItems = True
For Each PI In PT2.PivotFields(PF.Name).PivotItems
If Not IsError(Application.Match(PI.Caption, myfilter, 0)) Then
PI.Visible = True
Else
PI.Visible = False
End If
Next
Else
PT2.PivotFields(PF.Name).CurrentPage = myfilter
End If
Next
End Sub
It seems you have to loop through the PT1items to get each.
And also loop through PT2items to select those filters identified from PT1. HTH.
Note:If however, your using XL 2010 and up, just use a Slicer.
似乎您必须遍历PT1项目才能获得每个项目。
并且还循环遍历PT2项目以选择从 中识别的那些过滤器PT1。哈。
注意：但是，如果您使用 XL 2010 及更高版本，只需使用Slicer。
