A different technique is to turn on the first subtotal. This method automatically turns off
the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals
are suppressed:
PT.PivotFields(“Product”).Subtotals(1) = True
PT.PivotFields(“Product”).Subtotals(1) = False
Suppressing Grand Total for Rows
Because you are going to be using VBA code to add automatic subtotals, you can get rid of
the Grand Total row. If you turn off Grand Total for Rows, you delete the column called
Grand Total. Thus, to get rid of the Grand Total row, you must uncheck Grand Total for
Columns. This is handled in the code with the following line:
PT.ColumnGrand = False
Handling Additional Annoyances When Creating Your Final
Report
You’ve reached the end of the adjustments that you can make to the pivot table. To achieve
the final report, you have to make the remaining adjustments after converting the pivot
table to regular data.
Figure 11.9 shows the pivot table with all the adjustments described in the preceding sec-
tions and with
PT.TableRange2
selected.
Chapter 11
Using VBA to Create Pivot Tables
250
11
Figure 11.9
Getting 90% of the way
to the final report took
less than one second and
fewer than 30 lines of
code.To solve the last
five annoying problems,
you have to change this
data from a pivot table
to regular data.
Creating a New Workbook to Hold the Report
Say you want to build the report in a new workbook so that it can be easily mailed to the
product managers. Doing this is fairly easy. To make the code more portable, assign object
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 250
Summary of Contents for VBA
Page 60: ......
