PivotSelect and PivotSelection
The PivotSelect method selects part of a PivotTable. The syntax is:
PivotTableObject .PivotSelect(Name, Mode)
The Mode parameter specifies the selection mode and can be one of the following
XlPTSelectionMode constants:
Enum XlPTSelectionMode xlDataAndLabel = 0 xlLabelOnly = 1 xlDataOnly = 2 xlOrigin = 3 xlBlanks = 4 xlButton = 15
xlFirstRow = 256 ' Excel 9 only
End Enum
The Name parameter specifies the selection in what Microsoft refers to as "standard PivotTable selection format." Unfortunately, the documentation does not tell us what this means, saying instead, "A string expression used to specify part of a PivotTable. The easiest way to understand the required syntax is to turn on the macro recorder, select cells in the PivotTable, and then study the resulting code." There is more on this, and we refer the reader to the Excel VBA help documentation (start by looking up the PivotSelect topic).
So let us consider some examples, all of which are based on the pivot table in Figure 20-10. However, to illustrate the Name property and to shorten the figures a bit, we will rename the data field "Sum of Transactions" to "Trans" and "Sum of Sales" to "Sale" using the following code:
Sub Rename()
' To shorten the names of the data fields
ActiveSheet.PivotTables("Sales&Trans").
DataFields("Sum of Transactions").Name = "Trans"
ActiveSheet.PivotTables("Sales&Trans").
DataFields("Sum of Sales").Name = "Sale" End Sub
This also emphasizes a point we made earlier. Namely, we would like to rename the "Sum of Sales" field to "Sales" but there is a column in the source table by that name, so Excel will not let us use the name for a data field. Thus, we are stuck with "Sale." Now back to business.
The following code selects the entire pivot table:
ActiveSheet.PivotTables("Sales&Trans"). PivotSelect "", xlDataAndLabel
The following code selects the Store Type label area (pivot-field label and pivot-item labels):
ActiveSheet.PivotTables("Sales&Trans").
PivotSelect "'Store Type'[All]", xlLabelOnly
The following code selects all data and labels related to the Company pivot item:
ActiveSheet.PivotTables("Sales&Trans"). PivotSelect "Company", xlDataAndLabel
The following code selects the cells shown in Figure 20-19:
ActiveSheet.PivotTables("Sales&Trans").
PivotSelect "Company BOSTON", xlDataAndLabel
Figure 20-19. Selecting the company label and data for Boston
|
A |
B I |
C |
D |
E |
F 1 |
G I | ||
|
1 |
Y ear |(^JI) | |||||||
|
2 | ||||||||
|
3 |
|peroc ; | |||||||
|
4 |
Ôtone C ty I |
;>1ore Typg_______________ |
Data |
| 1 |
: |
3 |
4 | |
|
Ï |
BOSTON..... |
Tngrtî |
KKH |
23^14 |
2W2 |
25502 | ||
|
6 |
^HHH 1 |
Sale |
44*75 |
4Ô?27 |
4S250 |
45223 | ||
|
7 |
hrsfichw» |
Trans |
13945 |
1 yjih |
13^10 | |||
|
8 |
Sale |
2Îèiia |
18022 |
■IS ¿2i. | ||||
|
■j |
LCG'.flWSÏfUES...... |
CcilH'J^ity |
Trans |
■4-JÙÙ |
ssâse |
256E2 |
35001 | |
|
13 |
£al& |
123476 |
123564 |
'26340 |
12541S | |||
|
11 |
Frsich as |
Trans |
; i5Ô3 |
72347 |
75613 |
743-91 | ||
|
13 |
Salo |
2Ï24ÏI |
203274 |
273325 |
274531 | |||
|
13 |
NEW |
Company |
Trans |
2461G |
23104 |
27015 |
26954 | |
|
14 |
Sale |
7S08£ |
05251 |
È7QC5 |
S805S | |||
|
15 |
Frgichse |
Trans |
53273 |
54351 |
51522 |
5121? | ||
|
13 t -i |
Sale |
17ËJ53 |
1S2461 |
* 70537 |
137440 | |||
On the other hand, by reversing the words Company and BOSTON:
ActiveSheet.PivotTables("Sales&Trans").
PivotSelect "BOSTON Company", xlDataAndLabel we get the selection in Figure 20-20, which does not include the Company label!
Figure 20-20. Reversing the word order to select company data for Boston only
|
A |
B |
C |
D 1 |
E I |
F |
G | |
|
1 |
Year |
(All) | |||||
|
2 | |||||||
|
3 |
I Period | | ||||||
|
4 |
Stone Cily |3tcne Type |
Datu |
1 |
2 |
3 |
4| | |
|
5 |
BOSTON |
C^nUfinv |
TWIT |
2?7'4 |
231572 |
2S6Q2 | |
|
S |
Sete |
44£?£ |
4 ?>m\ | ||||
|
7 |
Francnise |
Irani |
ijysji |
13275 |
liilO | ||
|
& |
¿sit |
21S16 |
21739 |
13632 |
13325 | ||
|
9 |
LOii AHGELES |
Cc.inpan; |
Trani |
4 ESS |
J |
55C01 | |
|
10 |
Sate |
1I--47E. |
120564 |
' 26340 |
125418 | ||
|
11 |
^ntniw |
Tnsni. |
11 5S3 |
729^7 |
75C19 |
7^392 | |
|
12 ' |
Sete |
262431 |
258274 |
279325 |
¿"¿E31 | ||
|
13 |
NEW YORK |
Co-npany |
Trans |
26104 |
2701- |
4 | |
|
14 |
sue |
T3fJS9 |
(51751 |
SI905 | |||
|
15 |
Franchise |
Trans |
52272 |
54351 | |||
|
16 J |
isle |
176353 |
1i;2461 |
'70i ■ ■ |
W 446 | ||
The following code selects cell E12 of the pivot table in Figure 20-10:
ActiveSheet.PivotTables("Sales&Trans").PivotSelect _ "'LOS ANGELES' Franchise 'Sale' '2'", xlDataOnly
The following code selects the labels and data for Boston and New York:
ActiveSheet.PivotTables("Sales&Trans").
PivotSelect "'Store City'[BOSTON,'NEW YORK']", xlDataAndLabel
If we replace the comma with a colon:
ActiveSheet.PivotTables("Sales&Trans").
PivotSelect "'Store City'[BOSTON:'NEW YORK']", xlDataAndLabel then all items from Boston to New York (that is, all items) are selected.
The PivotSelection property returns or sets the PivotTable selection, again in standard PivotTable selection format. Setting this property is equivalent to calling the PivotSelect method with th e Mode argument set to xlDataAndLabel.
Average user rating: 1 stars out of 2 votes
Post a comment