Sorting Pivot Fields

The AutoSort method sets the automatic field-sorting rules for the pivot field. The syntax is:

PivotFieldObject .AutoSort( Order, Field)

The Order parameter specifies the sort order and is one of the following constants:

Enum XlSortOrder xlAscending = 1 xlDescending = 2 End Enum

It can also be set to xlManual to disable automatic sorting. The Field parameter is the name of the field to use as the sort key.

For instance, referring to Figure 20-10, the code:

ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Store Type").AutoSort _ xlAscending, "Sale"

sorts by Sale and produces the results shown in Figure 20-25. Note the order of the Store Type items for Boston as compared to the other cities.

Figure 20-25. Illustrating AutoSort

A

0

C

D

E F G

Year j(AJI)

Period

Store City

Store Type

Data

12 3 4

BOSTON

Franchise

Tr^ns Sale

13993 13942 13275 13210 21010 21739 13632 13325

Company

Trans Sale

23248 23714 23672 23602 44673 46927 46256 46223

LOS ANGELE?

Ccntip3iTiy

Trans Sale

34533 35933 35692 3500I ■23473 129564 126340 125413

Franchise-

Trans

71533 72947 756^9 74392

Sale

262431 268274 279325 274531

NEW YORK

Company

Trans Sale

24616 26104 27015 26854 3531 67905 88053

Franchise

Trans Sale

53273 54351 51822 51218 176353 182461 170537 16744-'"::

The read-only AutoSortField property returns the name of the key field and the AutoSortOrder property returns the sort order of the pivot field (xlAscending, xlDescending, or xlManual).

0 0

Post a comment