Programming Excel with VBA and .NET

Name

pivotfield.AutoShow(Type, Range, Count, Field)

Synopsis

Applies a filter to a pivot field.

Argument	Settings
Type	The setting xlAutomatic applies the filter; xlManual removes the filter.
Range	The setting xlTop shows the top Count of records; xlBottom shows the bottom Count of records.
Count	The number of records to show.
Field	The data field to use as the criterion of the filter.

These settings are equivalent to the Top 10 AutoShow options on the PivotTable Field Advanced Options dialog box, shown in Figure 13-20.

Figure 13-20. Setting advanced field options

The following code shows the bottom ProductName item based on the data field (sales rank, lower is better):

Sub ShowBestSeller(  )
    Dim pt As PivotTable, pf As PivotField
    ' Uncomment next line to create pivot table.
    'CreatePivotTable
    ' Get pivot table.
    Set pt = Worksheets("BookSales").PivotTables(1)
    ' Get pivot field.
    Set pf = pt.PivotFields("ProductName")
    ' Set autoshow.
    pf.AutoShow xlAutomatic, xlBottom, 1, pt.DataFields(1).name
End Sub