Set Pivot Table SubTotal Display Property To False

**JulyMoon** · 10-18-2013, 08:54 PM

Hi,

I have the following code to disable subtotals for my pivot table:

Code:

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Log Id").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Surgery Date").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("OR Room").Subtotals = Array _
    (False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Service").Subtotals = Array _
    (False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Surgeon").Subtotals = Array _
    (False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Location").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Post Op Dest").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Patient Class").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Month").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Quarter").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Half year").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)

ActiveSheet.PivotTables("SelectedDataPivot").PivotFields("Year").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)

This code works perfectly fine for all fields except for the first one ("Log Id"). I even recorded a macro and got the same code as above. The weird part is the "Log Id" field wroks fine if I use say 1 year data (about 15K rows) but if I use 3 years worth of data (about 47K rows) i get the error "unable to set subtotals property of pivot field class".

Does it matter how many rows I am using in my pivot table? If so is there a way around this?

Also, I am sure the code above is not the most efficient way to disable sub totals so is there a better way of doing this?

**Excel Fox** · 10-18-2013, 09:29 PM

Welcome to ExcelFox

Please wrap your codes with proper code tags. Instructions are clearly given.

Try this

Code:

Sub ExcelFox()

    Dim fld As PivotField
    For Each fld In ActiveSheet.PivotTables("SelectedDataPivot").PivotFields
        fld.Subtotals(1) = False
    Next fld
    
End Sub