To control subtotal visibility and calculation use the Subtotals property of the PivotField object. The index number controls the calculation performed as shown in the table below.
| Index | Meaning |
|---|---|
| 1 | Automatic |
| 2 | Sum |
| 3 | Count |
| 4 | Average |
| 5 | Max |
| 6 | Min |
| 7 | Product |
| 8 | Count Nums |
| 9 | StdDev |
| 10 | StdDevp |
| 11 | Var |
| 12 | Varp |
Dim ws As Worksheet
Set ws = ActiveSheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim PTcache As PivotCache
Dim PT As PivotTable
'Define the cache for the PivotTable
Set PTcache =wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Range("Sales_Data[#All]"),Version:=xlPivotTableVersion15)
'Create the PivotTable
Set PT =ws.PivotTables.Add(PivotCache:=PTcache, _
TableDestination:=Range("J1"),TableName:="Sales Analysis")
PT.AddFieldsRowFields:=Array("Region", "Branch"), _
ColumnFields:="Product",PageFields:="Product Category"
PT.AddDataFieldField:=PT.PivotFields("Revenue"), _
Caption:="No ofTransactions", Function:=xlCount
'Remove subtotals from Region
PT.PivotFields("Region").Subtotals(1) = False