Subtotals Method
This method returns or sets the display of a particular type of subtotal for the specified pivot field. It is valid for all fields other than data fields. The syntax is:
PivotFieldObject .Subtotals( Index)
|
Table 20-4. Values for the Subtotals Method's Index Parameter | |
|
Index |
Subtotal Type |
|
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 |
For instance, the following code requests a display of subtotals for both Sum and Count:
For instance, the following code requests a display of subtotals for both Sum and Count:
ActiveSheet.PivotTables("Sales&Trans").
PivotFields("Store City").Subtotals(2) = True ActiveSheet.PivotTables("Sales&Trans").
PivotFields("Store City").Subtotals(3) = True
We can also set the Subtotals property to an array of 12 Boolean values to set multiple subtotals. For instance, the following code displays all subtotals:
ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Store City").
Subtotals = Array(False, True, True, True, True, True, True, True, True, True, True, True)
Note that we set Automatic to False in this array, since if Automatic is set to True, then all other values are set to False (thus providing a quick way to set all subtotals to False).
If this argument is omitted, the Subtotals method returns an array containing a Boolean value for each subtotal.
Post a comment