How to show top 10 items in a PivotTable using VBA?

To display the top 10 items in a PivotTable using VBA, you need to apply a filter to the field of interest. Below is a step-by-step approach to achieve this. The example assumes that you have already created a PivotTable in your Excel workbook.

Sub ShowTop10ItemsInPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    ' Set the worksheet where the PivotTable is located
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your worksheet's name

    ' Set the PivotTable
    Set pt = ws.PivotTables("PivotTable1") ' Change "PivotTable1" to your PivotTable's name

    ' Set the field you want to filter
    Set pf = pt.PivotFields("FieldName") ' Change "FieldName" to the field you want to use for the Top 10 filter

    ' Clear any existing filters, then apply the Top 10 filter
    With pf
        .ClearAllFilters
        .AutoSort xlDescending, .Value, pt
        .PivotFilters.Add Type:=xlTopCount, DataField:=pt.DataFields(1), Value1:=10
    End With

    MsgBox "Top 10 items filter applied to the PivotTable."
End Sub

Explanation:

Worksheet and PivotTable Setup: The code assumes your PivotTable is on “Sheet1” and is named “PivotTable1.” You should change these names to match your worksheet and PivotTable names.
PivotField: You’ll specify the field you want to apply the Top 10 filter to. Replace `”FieldName”` with the name of the field you are interested in.
Filter Application:

The script first clears any existing filters for the specified PivotField.
It then applies a descending sort order (this ensures the top items are at the top based on value).
Finally, it applies a Top 10 filter based on the first data field in the PivotTable. If your PivotTable has multiple data fields, you may need to adjust the `pt.DataFields(1)` to the appropriate data field index.

Execution: Run this macro to apply the filter. You can do this by pressing `Alt + F8` in Excel, selecting `ShowTop10ItemsInPivotTable`, and clicking `Run`.

This will apply a Top 10 filter to your PivotTable, displaying only the top 10 items based on the selected field and data field.

facebooklinkedin