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).
Average user rating: 5 stars out of 1 votes
Post a comment