Delete detail subtotals in pivot tables (Excel 97)

Topic

New Reply

I have the following problem.
I am using VBA to create a pivot table. There are several data fileds selected, with several row items. I can disable creating row grand totals by .RowGrand = False, and the same for the column grand totals.
However, Excel creates subtotals for each data item which I can not disable from the VBA code. Is there any method or property that I can use here?

Reply | Quote

Replies

You can use something like :

    ActiveSheet.PivotTables("PivotTableNAme").PivotFields("FieldName").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False).

The array elements respresent each type of subtotal function (Sum, Count etc), except for th efirst element which sets Automatic On (True) or Off (False)

The above line should switch off subtotals by field for the given Field and in the given PT.

Andrew C

Reply | Quote

Thanks for your help.

I tried out what you suggested, and I got the error message: Unable to set the Subtotals property of the PivotField class

Here is the relevant part of the code:

with

With .PivotFields(“RSE”)

.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
‘in debug mode, it fails at that line with the above message
End With

end with

Regards,
K.

Reply | Quote