Pivot Select and Pivot Selection

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.

0 -3

Post a comment