Pivot Tables & VBA

Question

So I have this pivot table where I can sort upon "Plant Name". I could simply hard code functionalityin saying "Plant26", "Plant12" etc to know the plant names that can be sorted upon. However, is there a different way to access all of these names and place them in an array, instead of iterating through the few thousand lines and finding a difference in name within that column, then append them to an array?

Example:

    For i = 5 to lastrow
    Name = Cells(i, "A").Value
    If 
    'in array
    Else
    'add in array
    End If
    next i

However is there a faster way? Is it possible to obtain these plant names that could be sorted upon and place them into an array? Here is a pseudo example..

   With Worksheets("sheet2").PivotTables(1)
    For i = 1 To .PivotFields("Plant Name").Count
        MsgBox .PivotFields(i).Name
        'Add to array
    Next
End With

Answer 1

This will list the name of the plants and display the result array in Sheet3 :

Sub test_Amasian21()
Dim A()
ReDim A(1 To 1)
Dim i As Double

With Sheets("sheet2").PivotTables(1).PivotFields("Plant Name")
    For i = 1 To .PivotItems.Count
        A(UBound(A)) = .PivotItems(i).Name
        ReDim Preserve A(LBound(A) To UBound(A) + 1)
    Next i
End With
ReDim Preserve A(LBound(A) To UBound(A) - 1)

Sheets("sheet3").Range("A1").Resize(UBound(A), 1).Value = A
End Sub

Answer 2

This will get you started, it will show you all PivotItems in PivotField "Plant Name".

Note: it is recommended to avoid using ActiveSheet.

Option Explicit

Sub GetAllPlantNamefromPivot()

Dim PvtItm              As PivotItem
Dim PvtFld              As PivotField
Dim PlantArr()          As Variant
Dim count               As Long

Set PvtFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Plant Name")

' reset Plant Name Array count
count = 0

For Each PvtItm In PvtFld.PivotItems
    ReDim Preserve PlantArr(0 To count)
    PlantArr(count) = PvtItm
    count = count + 1
Next PvtItm

End Sub