253
Handling Additional Annoyances When Creating Your Final Report
‘ Do some basic formatting
‘ Autofit columns, bold the headings, right-align
Selection.Columns.AutoFit
Range(“A3”).EntireRow.Font.Bold = True
Range(“A3”).EntireRow.HorizontalAlignment = xlRight
Range(“A3:B3”).HorizontalAlignment = xlLeft
‘ Repeat rows 1-3 at the top of each page
WSR.PageSetup.PrintTitleRows = “$1:$3”
Adding Subtotals
Automatic subtotals are a powerful feature found on the Data menu. Figure 11.10 shows the
Subtotal dialog box. Note the option Page Break Between Groups.
If you were sure that you would always have three years and a total, the code to add subto-
tals for each Line of Business group would be the following:
‘ Add Subtotals by Product.
‘ Be sure to add a page break at each change in product
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6), _
PageBreaks:=True
11
Figure 11.10
Use automatic subtotals
because doing so
enables you to add a
page break after each
product.Using this fea-
ture ensures that each
product manager has a
clean report with only
her product on it.
Add page breaks
However, this code fails if you have more or less than three years. The solution is to use the
following convoluted code to dynamically build a list of the columns to total, based on the
number of columns in the report:
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 253
To Next Page
Loading...
Need help?
General
