Summary field headings modified
Adding Formatting to a PivotTable 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 PivotStyleLight1 formatting to the
PivotTable, as shown in Figure 6-24.
CHAPTER 6 n PIVOTTABLES 245
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
CHAPTER 6 n PIVOTTABLES 246
Comment out the second line of code in the ApplyTableStyle procedure and uncomment
the third line.
Pages:
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237