CODEWITHSUNDEEP
vbaexcel-2010pivot-table
ingalcala
ingalcala

Reputation: 1845

PivotTable Do not show subtotals

I am converting a table into Pivot and then to a CSV file. I want to remove the subtotals of the pivot table. So far: this is my progress.

MACRO CreatePivot:

Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("Employees").Select
Range("A1").Select

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard

' Specify row and column fields.
Set objField = objTable.PivotFields("Supplier")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Part #")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Tracking #")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Packing/Inv#")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("PO#")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Ship Date")
objField.Orientation = xlRowField

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("Qty")
objField.Orientation = xlDataField
objField.Function = xlSum


' Specify a page field.
Set objField = objTable.PivotFields("Carrier")
objField.Orientation = xlPageField


' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
    ActiveSheet.Delete
End If
Application.DisplayAlerts = True

End Sub

Also I just added MACRO DesigningPivotTable:

Sub DesigningPivotTable()
Dim PT As PivotTable
Set PT = ActiveSheet.PivotTables(1)
PT.TableRange1.Select
With PT
 .NullString = 0
 .RepeatAllLabels Repeat:=xlRepeatLabels
 .ColumnGrand = False
 .RowGrand = 0
 .PivotFields("Order #").Subtotals(1) = True
 .PivotFields("Order #").Subtotals(1) = False
End With
End Sub

According to Excel 2013 Pivot Table Data Crunching; you can turn on the first subtotal, and this method automatically disables all the other subtotals, hence: it is not working for me:

 .PivotFields("Order #").Subtotals(1) = True
 .PivotFields("Order #").Subtotals(1) = False

Upvotes: 8

Views: 64438

Answers (7)

Hemal
Hemal

Reputation: 1

The best option is to set property with field

.Subtotals(1) = False

Upvotes: 0

Hmerman6006
Hmerman6006

Reputation: 1913

Another way is to set each PivotField Subtotal attribute to false as you create the fields. It all depends on what method you use to create your Pivot table. As below method does not work when you use the CreatePivotTable() method from a Pivot Cache that was Set using the Add method.

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard
With objTable
    'Specify row and column fields.
    With .PivotFields("Supplier")
        .Subtotals(1) = False
        .Orientation = xlRowField
    End With
    With .PivotFields("Part #")
        .Subtotals(1) = False
        .Orientation = xlRowField
    End With
    With .PivotFields("Tracking #")
        .Subtotals(1) = False
        .Orientation = xlRowField
    End With
    With .PivotFields("Packing/Inv#")
        .Subtotals(1) = False
        .Orientation = xlRowField
    End With
    With .PivotFields("PO#")
        .Subtotals(1) = False
        .Orientation = xlRowField
    End With
    With .PivotFields("Ship Date")
        .Subtotals(1) = False
        .Orientation = xlRowField
    End With

    ' Specify a data field with its summary
    ' function and format.
    With .PivotFields("Qty")
        .Subtotals(1) = False
        .Orientation = xlDataField
        .Function = xlSum
    End With
End With

Upvotes: 0

dra_red
dra_red

Reputation: 442

I found I did not need to set the subtotal to 'true' first. Setting it directly to false was fine. Example below:

With PvtTbl
    For Each pvtFld In .PivotFields
        pvtFld.Subtotals(1) = False
    Next pvtFld
End With

Upvotes: 1

Danhadnagy
Danhadnagy

Reputation: 21

I think it's easier.

ActiveSheet.PivotTables("Supplier").RowGrand = False
ActiveSheet.PivotTables("Supplier").ColumnGrand = False

Upvotes: 0

Bojan Goldy
Bojan Goldy

Reputation: 21

.PivotFields("Pivot Field Name").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

it is not better is it just to make it clear

Upvotes: 2

robotik
robotik

Reputation: 2007

.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

surely sets all possible subtotals to false, in one go.

Upvotes: 1

ingalcala
ingalcala

Reputation: 1845

All, thanks to some more research I found the solution

Sub PivotTableLayout2b()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField

Set PvtTbl = ActiveSheet.PivotTables(1)

'hide Subtotals for all fields in the PivotTable .

With PvtTbl
 For Each pvtFld In .PivotFields
 pvtFld.Subtotals(1) = True
 pvtFld.Subtotals(1) = False
Next pvtFld
End With
End Sub

Upvotes: 25

Related Questions