Formatting Grouping and Sorting a Pivot Table Report

You can modify the display and format of a PivotTable programmatically by using a number of different properties of the PivotTable object. For example, you may want to reposition the fields within the PivotTable layout, sort the data by a specific field, or group your data by years, quarters, months, and so on. The example procedure below reformats the PivotTable report generated earlier in this appendix (see Figure A-6).

1. Add a new module to the current workbook and enter the procedure code, as shown below.

2. Activate the worksheet containing the PivotTable, as presented in Figure A-6.

3. Run the procedure to reformat the PivotTable shown in Figure A-6.

Sub FormatPivotTable()

Dim pvtTable As PivotTable Dim strPiv As String

If ActiveSheet.PivotTables.Count > 0 Then strPiv = ActiveSheet.PivotTables(1).Name Set pvtTable = ActiveSheet.PivotTables(strPiv)

Else

Exit Sub End If

With pvtTable

.PivotFields("OrderDate").Orientation = xlRows .PivotFields("CompanyName").Orientation = xlHidden

' use this statement to group OrderDate by year .PivotFields("OrderDate").DataRange.Cells(1).Group _ Start:=True, End:=True, _

periods:=Array(False, False, False, False, False, False, True)

' use this statement to group OrderDate both by quarter and year; ' if you use this statement, comment the preceding line of code ' .PivotFields("OrderDate").DataRange.Cells(1).Group _ Start:=True, End:=True, _

periods:=Array(False, False, False, False, False, True, True)

.PivotFields("OrderDate").Orientation = xlColumns .TableRange1.AutoFormat Format:=xlRangeAutoFormatColor2 .PivotFields("ProductName").DataRange.Select

' sort the Product Name field in descending order based on the Sum ' of Total

.PivotFields("ProductName").AutoSort xlDescending, "Sum of Total" Selection.IndentLevel = 2 With Selection.Font

.Name = "Times New Roman" .FontStyle = "Bold" .Size = 10 End With

With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With End Sub

By studying the code presented above, you can easily conclude that:

■ To change the layout of a PivotTable, you should set the Orientation property of the required field to a different constant. The example code above moves the OrderDate field from the Page area to the Row area of the PivotTable layout.

■ To display a PivotTable without a particular field, you need to set the Orientation property of the required field to xlHidden.

■ To group the OrderDate field by year, you should use the Group method of the Range object. For example, the code uses the following statement to group the data in the OrderDate field by year:

.PivotFields("OrderDate").DataRange.Cells(1).Group _ Start:=True, End:=True, _

periods:=Array(False, False, False, False, False, False, True)

■ The Start and End arguments specify the start and end date to be included in the grouping. By setting these arguments to True, all dates are included. The Periods argument is an array of Boolean values that specifies the period for the group, as shown in the following table:

Array Element Period

1 Seconds

2 Minutes

3 Hours

4 Days

5 Months

6 Quarters

7 Years

Note: The following statement will ungroup the dates:

ActiveSheet.PivotTables(1).PivotFields("OrderDate").LabelRange.Ungroup

■ You can apply automatic formatting to the entire PivotTable report by using the AutoFormat property of the Range object. The TableRange1 property returns a Range object that represents the range containing the entire PivotTable report without the page fields:

.TableRange1.AutoForirat Format:=xlRangeAutoFormatColor2

■ You can select the data items in a particular field by using the DataRange property and the Select method, like this:

.PivotFields("ProductName").DataRange.Select

■ You can sort a particular field in descending or ascending order. The example procedure uses the following statement to sort the ProductName field in descending order based on the Sum of Total:

.PivotFields("ProductName").AutoSort xlDescending, "Sum of Total"

■ You can change the text indentation, and the font name, size, and style, as well as the borders of the selected range, as demonstrated in the last statements of the example procedure.

10 Microsoft Excel - ProgramPivots.xls

H UnlJ

Fde Ecfit View Jnsefl Format Tools Data Bindow

Help U'i i uni i- i1 il i: • _ fl X

1 II SI

m - CJ •

: % r - :

86* I g

[¡mes New Roman . 10 - Mal / y fei

® * ffl

ï % f

täS

. - Ai - A

»

B7

fx Côte de Blaye

A

B

C

0

E

F

1

-H

' ï

3

Customer©

CAIIl -1

4

5

Sum ot Total

SKSZBB

&

PiörfuctWnriw

1996

1997

T3M

_

7

Côte de ü lave

$24,874.40

$49,193.08

$67,324.25

(141 ,398.73

3

$11,92950

$34,755.91

$33,683.26

$80.368.67

3

I-'| : 1 " ' ,li

19.035 40

$35.775.30

$56,345.00

$71,155.70

13

i ai lr .1 : u - , .

$9,603.75

$21 £38.30

$15,092.92

$47,234.97

1 '

CanUlbert Piexrot

19,02436

$20,505.40

$17,295.12

$46,825.48

J2_

. mi.. il ci im Alice

$2,76336

$32,604.00

$7,225.70

$42,593.06

13

Muüi ï il/ l'i'i'i! Apples

16.158 60

$24.570.30

$11.090.25

$41.819.65

M

Alice Mutttu

$6,93226

$17.604.60

$8,131.50

$32*98.38

15

Cuxianw ll|vr.

$4,725110

$15,950.00

$8,480.67

$28,171 87

15

iii il le S„u> lin,,!!

$4,757.48

$13,948.68

Ii ,390.48

$25 i 96.64

IL

MorzajeUa ' ' ' • " u.n i

16.772 33

$11,602.80

IS ,525.00

$24 SOO .13

18

ip-1. cüöllo

$4,931 20

|l 1*69.30

$7,525.60

$23,526.70

13

sir lioiiiir % Ma: , ,111.1,1 r

$6,54430

Ï7;314.30

$8,704.28

$22,563.38

20

Uncle »dis's l'i'r.ii.'.i IMed Peara

$552.00

[9,188.30

$12,306.00

$22,044.30

21

Tmiwn cuit ^enunelknode]

$3,205.30

18556.47

$10,696.19

$21 ,957.97

M h

L > n\shceM/SourceDaia / Sheetl ■/SJieEib? / Sfißebä y j < |

m -.in

Ready

Figure A-7: A PivotTable report can be reformatted to view data from a different perspective.

0 0

Post a comment