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)

where the optional Index parameter indicates the type of subtotal and is a number from Table 20-4.

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.

0 0

Post a comment