格式化枢轴表时运行时错误1004

Question

我有一个Excel枢轴表，我只想做两件事：(1)应用经典PivotTable布局，(2)不显示小计。为了帮助我编写代码，我录制了一个宏。运行宏时，我得到“运行时错误'1004'：无法获得工作表类的PivotTable属性。”

数据透视表是手动创建的。然后切换到数据透视，并尝试运行代码。

With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "A" _
        ).Subtotals = Array(False, False, False, False, False, False, False, False, False, False _
        , False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("B").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )

我的期望是代码应用经典PivotTable布局，然后删除可能存在的任何小计。

Answer 1

您的代码定位于ActiveSheet (当前可见的代码)，并且只在名称为"PivotTable1“的PivotTable驻留在其上时才能工作：

ActiveSheet.PivotTables("PivotTable1")

为了使它更具体或更一般，您可以按它的名称或索引来寻址一个工作表，也可以按它的名称或索引来确定它的枢轴，例如。

ActiveSheet.PivotTables(1)
ActiveWorkbook.Sheets(3).PivotTables("PivotTable1")
ActiveWorkbook.Sheets("My Sheet Name").PivotTables(1)

如果将PivotField的用户定义的小计切换为RowField或ColumnField，则只起作用，因此我建议显式地解决这些问题，并按如下方式循环处理所有这些小计：

Private Sub SwitchAllSubtotalsOnOrOff()
    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables(1)
    With pt
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow

        For Each pf In .RowFields
            'pf.Subtotals(1) = True   ' automatic on (= all other off)
            'pf.Subtotals(1) = False  ' automatic also off

            ' all 11 userdefined on (sum, count, average, max, min, product, count nums, stdev, stdevp, var, varp)
            'pf.Subtotals = Array(False, True, True, True, True, True, True, True, True, True, True, True)

            ' all 12 off (= none)
            pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        Next pf

        For Each pf In .ColumnFields
            If Not IsError(pf.GroupLevel) Then
                pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            End If
        Next pf

    End With
End Sub

Answer 2

与使用数组不同，您可以使用Field .PivotFields("A").Subtotals(1) = True将Subtotal默认设置为Automatic &然后在下一行中使用False关闭它。

代码：

With ActiveSheet.PivotTables("PivotTable1")

        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        .PivotFields("A").Subtotals(1) = True
        .PivotFields("A").Subtotals(1) = False
        .PivotFields("B").Subtotals(1) = True
        .PivotFields("B").Subtotals(1) = False

End With

这里有更多的信息

关闭每个字段的小计：

For Each fld In ActiveSheet.PivotTables("PivotTable1").PivotFields

    fld.Subtotals(1) = True
    fld.Subtotals(1) = False

Next