Determining Size of a Finished Pivot Table
Knowing the size of a pivot table in advance is difficult. If you run a report of transactional
data on one day, you may or may not have sales from the West region, for example. This
could cause your table to be either six or seven columns wide. Therefore, you should use the
special property
TableRange2 to refer to the entire resultant pivot table.
Because of the limitations of pivot tables, you should generally copy the results of a pivot
table to a new location on the worksheet and then delete the original pivot table. The code
in
CreateSummaryReportUsingPivot() creates a small pivot table. Note that you can set the
ColumnGrand and RowGrand properties of the table to False to prevent the totals from being
added to the table.
PT.TableRange2 includes the entire pivot table. In this case, this includes the extra row at the
top with the button Sum of Revenue. To eliminate that row, the code copies
PT.TableRange2
but offsets this selection by one row by using .Offset(1, 0). Depending on the nature of
your pivot table, you might need to use an offset of two or more rows to get rid of extrane-
ous information at the top of the pivot table.
The code copies
PT.TableRange2 and uses PasteSpecial on a cell five rows below the cur-
rent pivot table. At that point in the code, your worksheet appears as shown in Figure 11.6.
The table in R2 is a live pivot table, and the table in R10 is just the copied results.
Chapter 11 Using VBA to Create Pivot Tables
244
11
Figure 11.6
An intermediate result of
the macro.Only the sum-
mary in R10:V14 will
remain after the macro
finishes.
You can then totally eliminate the pivot table by applying the Clear method to the entire
table. If your code is then going on to do additional formatting, you should remove the
pivot cache from memory by setting
PTCache equal to Nothing.
The following code will use a pivot table to produce a summary from the underlying data.
At the end of the code, the pivot table will be copied to static values and the pivot table will
be cleared.
Listing 11.2 Code to Produce a Static Summary from a Pivot Table
Sub CreateSummaryReportUsingPivot()
‘ Use a Pivot Table to create a static summary report
‘ with model going down the rows and regions across
Dim WSD As Worksheet
Dim PTCache As PivotCache
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 244
To Next Page
Loading...
Need help?
General
