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