VBA将枢轴滤波器设置为单选择

Resolved my original problem from earlier today - now I have working code that isn't producing quite as polished a result as I'd like. In a nutshell, I'm looping through all sheets and setting all pivots to be filtered to a single value. I don't want multiple values unless the filter is cleared and showing everything. My code below shows the desired data but the filter on the pivot itself displays as Multiple Values (with the one value checked). How can I get it to treat this as a single selection, and show that single value on the pivot filter?

从今天早些时候解决了我最初的问题——现在我的工作代码并没有像我想的那样得到完美的结果。简单地说，我遍历所有的表，并将所有的数据点设置为一个单一值。我不想要多个值，除非过滤器被清除并显示所有内容。下面的代码显示所需的数据，但是pivot上的过滤器本身显示为多个值(选中一个值)。我怎么才能让它把这个当作一个选择，然后在主过滤器上显示那个值呢?

Sub Loop_Complex_bkup()
Dim wks As Worksheet, strName As String, pvt As PivotTable, strPvtName As String

For Each wks In Worksheets
strName = wks.Name
For Each pvt In wks.PivotTables
    strPvtName = pvt.Name
    With Sheets(strName).PivotTables(strPvtName).PivotFields("OrderSubType")
        .ClearAllFilters
        For Each PivotItem In .PivotItems
            If PivotItem = "Complex" Then PivotItem.Visible = True
            If PivotItem <> "Complex" Then PivotItem.Visible = False
        Next
    End With
Next
Next

Set wks = Nothing
Set pvt = Nothing

End Sub

2 个解决方案

#1

Since I had a similar issue I finally resolved, I decided to post the solution(s) I found for anyone still searching. The code I used was:

由于我最终解决了一个类似的问题，所以我决定将我找到的解决方案发布给那些仍在搜索的人。我使用的代码是:

With Sheets(strName).PivotTables(strPvtName).PivotFields("OrderSubType")
    .ClearAllFilters
    .CurrentPage = "Complex"
End With

The .CurrentPage selects a single filter item and defaults the .EnableMultiplePageItems = False

currentpage选择一个筛选项，并默认为.EnableMultiplePageItems = False

I am also not sure how the filter displays are affected between pivot tables connected through a slicer, so I would recommend that the EnableMultiplePageItems be set the same between such tables.

我也不确定过滤器在通过切片器连接的数据透视表之间是如何显示的，所以我建议在这些表之间设置EnableMultiplePageItems相同。

Note: for anyone with the same problem, except for wanting .EnableMultiplePageItems = True

注意:对于任何有相同问题的人，除了需要。enablemultiplepageitems = True。

I had to use the following code to fix the filter display.

我必须使用以下代码来修复过滤器显示。

With Sheets(strName).PivotTables(strPvtName).PivotFields("OrderSubType")
    .ClearAllFilters
    .CurrentPage = "Complex"
    .EnableMultiplePageItems = True

    For Each PI In .PivotItems
        If PI.Name <> "Complex" Then PI.Visible = False
    Next PI
End With

I know it looks redundant, but the display wouldn't fix itself until I added the .CurrentPage and .EnableMultiplePageItems lines.

我知道它看起来是多余的，但是直到我添加了. currentpage和. enablemultiplepageitems行之后，显示才会自我修复。

#2

The command you're looking for is .EnableMultiplePageItems. If I'm understanding the question correctly, this is what you're looking for:

您正在寻找的命令是. enablemultiplepageitems。如果我理解正确的话，这就是你要找的:

        With Sheets(strName).PivotTables(strPvtName).PivotFields("OrderSubType")
            .ClearAllFilters
            .EnableMultiplePageItems = False

#1