expression.FilterOnScope
expression Required. An expression that returns a PivotField object.
Remarks
The scope is based on one or more members of a different field than the field that is being filtered. For example, you may want to apply a filter to the Customer field, which has been added to the row axis of your PivotTable list, to display the three customers to whom you have made the most sales. To do this, you set the FilterFunction property to plFilterFunctionTopCount, the FilterFunctionValue property to 3, and the FilterOn property to the Total Sales total. The PivotTable list displays your three best customers.
If you need to narrow your query to a more specific set of customers, then you would use the FilterOnScope property. If you want to see the your top customers in the Southeast region, then you would set the FilterOnScope property to an expression that evaluates to the Southeast member in the Region field.
Example
This example displayes the two top-selling products in Canada.
Sub DisplayTopTwoCanadianSellers()
Dim objPivotView
Dim objPivotData
Dim fldProductName
' Set a variable to the active view of the PivotTable.
Set objPivotView = PivotTable1.ActiveView
' Set a variable to the active data of the PivotTable.
Set objPivotData = PivotTable1.ActiveData
' Set a variable to the Product Name field, which has been
' added to the row axis of the PivotTable list.
Set fldProductName = objPivotData.RowAxis.Fields("Product Name")
' The following two lines of code set the PivotTable list to filter for the
' top 2 items.
fldProductName.FilterFunction = PivotTable1.Constants.plFilterFunctionTopCount
fldProductName.FilterFunctionValue = 2
' Filter based on the Unit Sales total.
Set fldProductName.FilterOn = objPivotView.Totals("Unit Sales")
' Set the filter scope to include only sales in Canada.
fldProductName.FilterOnScope = objPivotView.FieldSets("Store").Member.ChildMembers("Canada").UniqueName
End Sub