Set Pivot Table SubTotal Display Property To False

[JulyMoon]

Hi,

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



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]

Welcome to ExcelFox

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

Try this



Sub ExcelFox()

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

End Sub