Adding Formatting to a Pivot Table Report

The default PivotTable report Excel generates looks okay, but Excel 2007 does provide us with 75 different formatting options. To change the look of a PivotTable report using VBA code, use the PivotTable object's TableStyle2 property. This property is named TableStyle2 because there is already a TableStyle property (but it's not a member of the PivotTable object's properties—go figure).

Add a new subroutine to a standard code module and add the following code:

Sub ApplyTableStyle()

ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight1" 'ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight22" 'ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleMedium23" End Sub

Before we run this code, let's remove the Item Qty field from the Values list in the PivotTable Field List to make the PivotTable smaller and the formatting easier to see.

Run the code from the Macros dialog box to apply the PivotStyleLightl formatting to the PivotTable, as shown in Figure 6-24.

Figure 6-24. PivotStyleLight1 formatting applied

Comment out the first line of code in the ApplyTableStyle procedure and uncomment the second line. Run the subroutine from the Macros dialog box to apply PivotStyleLight22 formatting, as shown in Figure 6-25.

Figure 6-25. PivotStyleLight22 formatting applied

Comment out the second line of code in the ApplyTableStyle procedure and uncomment the third line. Run the subroutine from the Macros dialog box to apply PivotStyleMedium23 formatting, as shown in Figure 6-26.

Item Amount

Row Labels T

Cherry Hill Trenton =! NY

Albany Buffalo New York Syracuse Trenton Rochester -PA

Philadelphia Reading Grand Total

Column Labels CD

Tank Top

T-Shirt

Grand Total

1240

492

675

2407

630

372

60

1062

610

120

615

1345

3540

3542

7875

14957

90

492

1170

1752

990

624

1215

2829

1430

1382

2910

5722

aso

60

240

1150

90

492

1170

1752

90

492

1170

1752

1800

324

3510

5634

1510

324

2340

4174

290

0

1170

1460

6580

4358

12060

22998

Figure 6-26. PivotStyleMedium23 formatting applied

0 0

Post a comment