Grouping

Excel also lets us group (and ungroup) the data for a selection of pivot items into a single new pivot item. This is done using the Group and Ungroup methods of the Range object. Note that these methods apply to the Range object, not the PivotField or PivotItem objects.

The Group method has two syntaxes, but we will look at only the more flexible of the two. (For all of the details on the Group method, we refer the interested reader to the Excel help documentation.)

Let us look at an example. Referring as usual to the pivot table in Figure 20-10, the following code selects all labels and data for Boston and New York and then groups this data into a single group. The group is then renamed Eastern. The results are shown in Figure 20-27. Observe that Excel creates both a new pivot field and a new pivot item. The pivot field is called Store City2 and contains the existing Los Angeles pivot item along with a new pivot item, which would have been given the name Group1 by Excel if we had not specified the name Eastern.

With ActiveSheet.PivotTables("Sales&Trans")

.PivotSelect "'Store City'[BOSTON,'New York']", xlDataAndLabel

Set rng = Selection rng.Group

.PivotFields("Store City2").PivotItems(1). Name = "Eastern" .PivotSelect "Eastern", xlDataAndLabel End With

Figure 20-27. Illustrating the Group method

A

B

C

D

E

F

G

H

1

Year

(All}

?

3

Per od

4

Store City2

Store Ciiy

Store

Data

1

2

3

4

5

Eastern

BOSTON

comp

Trans

26243

26714

28072

2S602

3

S aie

44578

46?27

46256

4?22î

7

Franc

"Trans

13993

13942

13275

13210

Säe

21816

2i/ay

18032

183Ä

NEW yorj

Comp

Trans

24516

26104

270 i 5

26854

Sete

7c0?y

£5251

879D5

P3P5S

11

Frön':

Trans

53273

5435 I

51322

51218

'2

Sale

182461

170557

167446

L OR ANGFI F $

I OR ANGFI

Cmmp

Trani

3453S

sans?

35001

'4

Saâê

123478

120564

126340

125418

franc

"Trans

71533

72947

75619

74o92

'0

Säe

263431

268274

279325

274531

0 0

Post a comment