循环数据透视表并删除相同的值

I am trying to loop through all pivot tables in a sheet and remove all values fields in them which have the same name: "Total Net Spend" and "% Split" (see picture for reference).

我试图遍历工作表中的所有数据透视表并删除其中具有相同名称的所有值字段:“总净支出”和“%拆分”(参见图片)。

I am trying the below code but it will only work on the first pivot and won't loop through all of them. How do I edit the code so that it will remove "Total Net Spend" and "% Split" columns on all the pivot tables in the sheet?

我正在尝试下面的代码,但它只适用于第一个数据透视表,不会循环遍历所有这些代码。如何编辑代码,以便删除工作表中所有数据透视表上的“总净支出”和“%拆分”列?

Sub Loop_Pivots()

Dim PT As PivotTable, PTField As PivotField

Set PT = Sheets("Sheet1").PivotTables("Pivot1")
With PT
    .ManualUpdate = True
    For Each PTField In .DataFields
        PTField.Orientation = xlHidden
    Next PTField
    .ManualUpdate = False
End With
Set PT = Nothing

End Sub

2 个解决方案

#1

To loop through the PivotTables try another for each loop like this

要遍历数据透视表,请为每个循环尝试另一个

Sub Loop_Pivots()

    Dim PT As PivotTable, PTField As PivotField
    For Each PT In Sheets("Sheet1").PivotTables
        With PT
            .ManualUpdate = True
            For Each PTField In .DataFields
                PTField.Orientation = xlHidden
            Next PTField
            .ManualUpdate = False
        End With
    Next PT
    Set PT = Nothing
End Sub

#2

Try the code below:

请尝试以下代码:

Option Explicit

Sub Loop_Pivots()

Dim PT          As PivotTable
Dim PTField     As PivotField

For Each PT In Sheets("Sheet1").PivotTables

    With PT
        .ManualUpdate = True
        For Each PTField In .PivotFields '<-- loop through all pivot fields
            Select Case PTField.Name
                Case "Total Net Spend", "% Split"  '<-- if Field Name equals on of the 2 in this case
                    PTField.Orientation = xlHidden
            End Select
        Next PTField
        .ManualUpdate = False
    End With
    Set PT = Nothing
Next PT

End Sub

#1