Run-time error 1004 while formatting a pivot table

Question

I have an Excel pivot table on which I want to do just two things: (1) apply the Classic PivotTable layout, and (2) to not show Subtotals. To help me with the code, I recorded a macro. When running the macro, I get the "Run-time error '1004': Unable to get the PivotTable property of the Worksheet class."

The pivot table is created manually. Then I switch to the Pivot and try to run the code.

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 _
        )

My expectation is that the code applies the Classic PivotTable layout, and then to remove any subtotals there may be.

Answer 1

Instead of using an Array you can set Subtotal Default to Automatic using Field Property .PivotFields("A").Subtotals(1) = True & then in the next line turn it off using False.

Code:

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

More Information Here

---

To turn off Subtotals from every Field:

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

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

Next

Answer 2

Your code addresses the ActiveSheet (the currently visible one) and works only, if a PivotTable with the name "PivotTable1" resides on it:

ActiveSheet.PivotTables("PivotTable1")

To get it more specific or more general, you may address a sheet by its name or index, and also the pivottable by its name or index too, e. g.

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

---

Switching the userdefined subtotals of a PivotField only works, if that pivot field is used as RowField or ColumnField, so I suggest to address those explicitly, and loop over all of them like this:

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