How to extract unique values from a PivotTable using VBA?

To extract unique values from a PivotTable using VBA, you can follow these general steps. The general idea is to iterate through the PivotTable fields and collect the unique items. Below is a basic example to help guide you through the process:

Sub ExtractUniqueValuesFromPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim uniqueValues As Collection
    Dim item As Variant
    Dim outputRange As Range
    Dim i As Integer

    ' Assuming the PivotTable is on the active worksheet
    ' and it's the first PivotTable on the worksheet
    Set ws = ActiveSheet
    Set pt = ws.PivotTables(1)

    ' Specify the pivot field you are interested in
    Set pf = pt.PivotFields("YourPivotFieldName")

    ' Initialize a new collection for unique values
    Set uniqueValues = New Collection

    ' Loop through each PivotItem in the PivotField
    On Error Resume Next ' Ignore errors when adding duplicate items
    For Each pi In pf.PivotItems
        uniqueValues.Add pi.Name, pi.Name
    Next pi
    On Error GoTo 0 ' Turn error handling back to normal

    ' Specify where you want to output the unique values
    Set outputRange = ws.Range("G1") ' Change this to your desired output range

    ' Output unique values
    i = 0
    For Each item In uniqueValues
        outputRange.Offset(i, 0).Value = item
        i = i + 1
    Next item
End Sub

Explanation:

Initialization:

We define variables to hold references to the worksheet, PivotTable, PivotField, and other necessary objects.

Select Target PivotTable:

Identify the worksheet and the specific PivotTable you’re operating on. This example assumes the first PivotTable on the active worksheet.

Select PivotField:

Assign the specific pivot field from which you want to extract unique values.

Extract Unique Values:

Use a `Collection` for storing unique values. Attempt to add each pivot item to the collection using its name as the key. Collections in VBA inherently handle duplicates, so any attempt to add a duplicate key will be skipped.

Output Values:

Print the unique values starting from a specified cell (e.g., “G1”).

Notes:

Replace `”YourPivotFieldName”` with the actual name of the field you want to extract values from.
Adjust the `outputRange` location to fit your needs regarding where the results should appear.

This VBA code should provide a good starting point for extracting unique values from a PivotTable. Adjust paths, field names, and ranges as appropriate to fit within your specific workbook and data structure.

facebooklinkedin