MsgBox “Detail reports for top 3 customers have been created.”
End Sub
Creating Reports for Each Region or Model
A pivot table can have one or more Report Filter fields. A Report Filter field goes in a sepa-
rate set of rows above the pivot report. It can serve to filter the report to a certain region,
certain model, or certain combination of region and model.
In VBA, Report Filter fields are called page fields.
To set up a page field in VBA, add the
PageFields parameter to the AddFields method. The
following line of code creates a pivot table with Region in the page field:
PT.AddFields RowFields:= “Product”, ColumnFields:= “Data”, PageFields:= “Region”
The preceding line of code sets up the Region page field with the value (All), which returns
all regions. To limit the report to just the North region, use the
CurrentPage property:
PT.PivotFields(“Region”).CurrentPage = “North”
One use of a page field is to build a user form in which someone can select a particular
region or particular product. You then use this information to set the
CurrentPage property
and display the results of the user form.
Another interesting use is to loop through all
PivotItems and display them one at a time in
the page field. You can quickly produce top 10 reports for each region using this method.
To determine how many regions are available in the data, use
PT.PivotFields(“Region”).PivotItems.Count. Either of these loops would work:
For i = 1 To PT.PivotFields(“Region”).PivotItems.Count
PT.PivotFields(“Region”).CurrentPage = _
PT.PivotFields(“Region”).PivotItems(i).Name
PT.ManualUpdate = False
PT.ManualUpdate = True
Next i
For Each PivItem In PT.PivotFields(“Region”).PivotItems
PT.PivotFields(“Region”).CurrentPage = PivItem.Name
PT.ManualUpdate = False
PT.ManualUpdate = True
Next PivItem
Of course, in both of these loops, the three region reports fly by too quickly to see. In prac-
tice, you would want to save each report while it is displayed.
So far in this chapter, you have been using
PT.TableRange2 when copying the data from the
pivot table. The
TableRange2 property includes all rows of the pivot table, including the
page fields. There is also a
.TableRange1 property, which excludes the page fields. You can
use either statement to get the detail rows:
Chapter 11 Using VBA to Create Pivot Tables
272
11
Listing 11.9 Continued
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 272
To Next Page
Loading...
Need help?
General
