To clear the filter from the Branch field, use the ClearAllFilters method:
PT.PivotFields(“Branch”).ClearAllFilters
To apply a date filter to the invoice date field to find records from this week, use this code:
PT.PivotFields(“InvoiceDate”).PivotFilters.Add Type:=xlThisWeek
The value filters allow you to filter one field based on the value of another field. For exam-
ple, to find all the branches where the total revenue is over $100,000, you would use this
code:
PT.PivotFields(“Branch”).PivotFilters.Add _
Type:=xlValueIsGreaterThan, _
DataField:=PT.PivotFields(“Sum of Revenue”), _
Value1:=100000
Other value filters might allow you to specify that you want branches where the revenue is
between $50,000 and $100,000. In this case, you would specify one limit as
Value1 and the
second limit as
Value2:
Chapter 11 Using VBA to Create Pivot Tables
280
11
Figure 11.23
You can easily choose all
the Branch items that
meet your criteria.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 280
To Next Page
Loading...
Need help?
General
