The Top 5 Markets report actually contains two snapshots of a pivot table. After using the
AutoShow feature to grab the top five markets with their totals, the macro went back to the
pivot table, removed the AutoShow option, and grabbed the total of all markets to produce
the Total Company row.
Using ShowDetail to Filter a Recordset
Take any pivot table in the Excel user interface. Double-click any number in the table. Excel
inserts a new sheet in the workbook and copies all the source records that represent that
number. In the Excel user interface, this is a great way to perform a drill-down query into a
dataset.
The equivalent VBA property is
ShowDetail. By setting this property to True for any cell in
the pivot table, you generate a new worksheet with all the records that make up that cell:
PT.TableRange2.Offset(2, 1).Resize(1, 1).ShowDetail = True
Listing 11.9 produces a pivot table with the total revenue for the top three stores and
ShowDetail for each of those stores. This is an alternative method to using the Advanced
Filter report. The results of this macro are three new sheets. Figure 11.20 shows the first
sheet created.
Chapter 11 Using VBA to Create Pivot Tables
270
11
Figure 11.20
Pivot table applications
are incredibly diverse.
This macro created a
pivot table of the top
three stores and then
used the
ShowDetail
property to retrieve the
records for each of those
stores.
Listing 11.9 Code Used to Create a Report for Each of the Top 3 Customers
Sub RetrieveTop3CustomerDetail()
‘ Listing 11.9
‘ Retrieve Details from Top 3 Stores
Dim WSD As Worksheet
Dim WSR As Worksheet
Dim WBN As Workbook
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets(“PivotTable”)
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 270
To Next Page
Loading...
Need help?
General
