Note that you use the Paste Special option to paste just values and number formats. This
gets rid of both borders and the pivot nature of the table. You might be tempted to use the
All Except Borders option under Paste, but this keeps the data in a pivot table, and you
won’t be able to insert new rows in the middle of the data.
Filling the Outline View
The report is almost complete. You are nearly a Data, Subtotals command away from having
everything you need. Before you can use the Subtotals command, however, you need to fill
in all the blank cells in the Outline view of column A.
Fixing the Outline view requires just a few obscure steps. Here are the steps in the user
interface:
1. Select all the cells in column A that make up the report.
2. Select Home, Editing, Find & Select, Go To Special to bring up the Go To Special dia-
log box. Select Blanks to select only the blank cells.
3. Enter an R1C1-style formula to fill the blank with the cell above it. This formula is
=R[1]C. In the user interface, you would type an equal sign, press the up-arrow key, and
then press Ctrl+Enter.
4. Reselect all the cells in column A that make up the report. This step is necessary
because the Paste Special step cannot work with noncontiguous selections.
5. Copy the formulas in column A and convert them to values by choosing Home,
Clipboard, Paste, Paste Values.
Fixing the Outline view in VBA requires fewer steps. The equivalent VBA logic is shown
here:
1. Find the last row of the report.
2. Enter the formula =R[-1]C in the blank cells in A.
3. Change those formulas to values. The code to do this follows:
Dim FinalReportRow as Long
‘ Fill in the Outline view in column A
‘ Look for last row in column B since many rows
‘ in column A are blank
FinalReportRow = WSR.Cells(Rows.Count, 2).End(xlUp).Row
With Range(“A3”).Resize(FinalReportRow - 2, 1)
With .SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = “=R[-1]C”
End With
.Value = .Value
End With
Handling Final Formatting
The last steps for the report involve some basic formatting tasks and then adding the sub-
totals. You can bold and right-justify the headings in row 3. Set up rows 1–3 so that the top
three rows print on each page:
Chapter 11 Using VBA to Create Pivot Tables
252
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 252
To Next Page
Loading...
Need help?
General
