Set Pivot Table SubTotal Display Property To False
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?
