Turning off Excel Pivot Table Sub Totals VB.NET
I am creating pivot tables in VB.NET and have run into a problem I did not think would be as difficult as it is turning out. When I create a pivot table it adds in subtotals for each row and I do not want that. In excel you just drag down the subtotals option and tell it to not display subtotals. I looked into the VBA for it and it is several lines long of this format:
ActiveSheet.PivotTables("Main Highway Pivot").PivotFields("Division"). _ Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _ False, False)
This is a surprising amount of code, but that is fine, so I have been trying something similar with my pivot table in VB.NET
With ptTable .ManualUpdate = True .PivotFields("Division").orientation = Excel.XlPivotFieldOrientation.xlRowField .PivotFields("Net Sales Amt").orientation = Excel.XlPivotFieldOrientation.xlDataField .PivotFields("Division").Subtotals = false .ColumnGrand = False .RowGrand = True .ManualUpdate = False End With
I have tried several different variations of the
.PivotFields("Division").Subtotals = false
line, but have had little luck. Is this the right code and I am specifying the false part wrong? Why is there no
.subtotal = false
to make this easier?
In any case, it seemed like something simple to me, but after a good bit of digging I am not sure how to fix it, so any help would be appreciated.
Thank you as always SO
Here's a snippet of VBA (from http://www.pcreview.co.uk/forums/hide-subtotals-rowfields-pivot-table-t974940.html) which might help:
'========================= Sub NoSubtotals() 'turns off subtotals in pivot table '.PivotFields could be changed to '.RowFields or .ColumnFields Dim pt As PivotTable Dim pf As PivotField On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields 'First, set index 1 (Automatic) to True, 'so all other values are set to False pf.Subtotals(1) = True pf.Subtotals(1) = False Next pf Next pt End Sub '========================