CODEWITHSUNDEEP
excelvbapivot-table
darwin
darwin

Reputation: 11

retrieving the values of the pivotItems that were set to true?

I recorded this macro below.

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")

    .PivotItems("3/15/2020").Visible = False
    .PivotItems("3/17/2020").Visible = True
    .PivotItems("3/18/2020").Visible = True
    .PivotItems("3/20/2020").Visible = True
End With

Is there a way to get the values of the pivotItems that were set to true? I would like to place them in an array to manipulate my worksheet.

Upvotes: 0

Views: 168

Answers (1)

Domenic
Domenic

Reputation: 8104

First, you can use the VisibleItems property of the PivotField object to get a collection of visible pivotitems...

Dim visiblePivotItems As PivotItems
Set visiblePivotItems = ActiveSheet.PivotTables("PivotTable2").PivotFields("Date").VisibleItems

Then, you can loop through the collection as follows...

Dim currentPivotItem As PivotItem

For Each currentPivotItem In visiblePivotItems
    Debug.Print currentPivotItem.Caption
Next currentPivotItem

Alternatively, you can iterate through the collection using the Item property...

Dim i As Long

With visiblePivotItems
    For i = 1 To .Count
        Debug.Print .Item(i).Caption
    Next i
End With

Upvotes: 1

Related Questions