使用 VBA 设置所有透视字段可见 = False？

Setting all Pivot Fields Visible = False using VBA?

提问by user2007848

I am trying to set all items in a pivot table fields visible = false. E.g I have 5 divisions in each country called DivA, DivB, DivC, DivD and DivE. Now sometimes in my source data i have more or less than the above mentioned division so I want to turn all otions off under pivotfield"Division" and then make visible all the above mentioned divisions if they are there (sometimes there might be 3, 4 or all 5 divisions).

我正在尝试在数据透视表字段中设置所有项目visible = false。例如，我在每个国家有 5 个部门，称为 DivA、DivB、DivC、DivD 和 DivE。现在有时在我的源数据中，我有比上述部门更多或更少的部门，所以我想关闭数据透视字段“部门”下的所有选项，然后使所有上述部门都可见（有时可能有 3、4或所有 5 个部门）。

I found a code online and trying to incorporate it but it keeps giving me error of setting "False = False".

我在网上找到了一个代码并试图合并它，但它一直给我设置“False = False”的错误。

Any Help would be much appreciated!

任何帮助将非常感激！

Please see my code below:

请看我下面的代码：

Sub test()
'
' test Macro
'


   With ActiveSheet.PivotTables("PivotTable3").PivotFields("Division")

    Dim Table As PivotTable
    Dim FoundCell As Object
    Dim All As Range
    Dim PvI As PivotItem

    Set All = Worksheets("Sheet1").Range("A7:AZ10000")
    Set Table = Worksheets("Sheet1").PivotTables("PivotTable3")
    For Each PvI In Table.PivotFields("Division").PivotItems
        Set FoundCell = All.Find(PvI.Name)
        If FoundCell <> "itemname" Then
            PvI.Visible = False
        End If
    Next

        .PivotItems("DivA").Visible = True
        .PivotItems("DivB").Visible = True
        .PivotItems("DivC").Visible = True
        .PivotItems("DivD").Visible = True
        .PivotItems("DivE").Visible = True


    End With
End Sub

回答by Siddharth Rout

Is this what you are trying? (UNTESTED)

这是你正在尝试的吗？（未经测试）

Sub test()
    Dim table As PivotTable
    Dim PvI As PivotItem

    Set table = Worksheets("Sheet1").PivotTables("PivotTable3")

    With table.PivotFields("Division")
        For Each PvI In .PivotItems
            Select Case PvI.Name
            Case "DivA", "DivB", "DivC", "DivD", "DivE"
                PvI.Visible = True
            Case Else
                PvI.Visible = False
            End Select
        Next
    End With
End Sub

回答by user4295090

I tested the answer macro...it works.

我测试了答案宏......它有效。

Sub Pivot_Test()

Sheets("Macro").Select
Dim Dt As String
Dt = Range("C1")

Dim PMth1 As String
Dim PMth2 As String
Dim PMth3 As String
Dim PMth4 As String
Dim PMth5 As String
Dim PMth6 As String
Dim PMth7 As String
Dim PMth8 As String
Dim PMth9 As String
Dim PMth10 As String
Dim PMth11 As String
Dim PMth12 As String

PMth1 = Range("J1")
PMth2 = Range("J2")
PMth3 = Range("J3")
PMth4 = Range("J4")
PMth5 = Range("J5")
PMth6 = Range("J6")
PMth7 = Range("J7")
PMth8 = Range("J8")
PMth9 = Range("J9")
PMth10 = Range("J10")
PMth11 = Range("J11")
PMth12 = Range("J12")




Sheets("MANCO").Select

Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim PI1 As PivotItem
Dim PI2 As PivotItem

Set pt1 = Worksheets("MANCO").PivotTables("Resolution")

With pt1.PivotFields("Month")
    For Each PI1 In .PivotItems
        Select Case PvI.Name
        Case PMth1, PMth2, PMth3, PMth4, PMth5, PMth6, PMth7, PMth8, PMth9, PMth10, PMth11, PMth12
            PI1.Visible = True
        Case Else
            PI1.Visible = False
        End Select
    Next
End With

Set pt2 = Worksheets("MANCO").PivotTables("Complaints")


With pt2.PivotFields("Month")
    For Each PI2 In .PivotItems
        Select Case PI2.Name
        Case PMth1, PMth2, PMth3, PMth4, PMth5, PMth6, PMth7, PMth8, PMth9, PMth10, PMth11, PMth12
            PI2.Visible = True
        Case Else
            PI2.Visible = False
        End Select
    Next
End With

End Sub

结束子