Getting values of a pivot table field into a string/array in VBA

[VBAguy22]

Hi
I have a pivot table PivotX on Sheet1 of my Workbook.
I need to be able to retrieve all the values in the pivot table's field Names into some sort of VBA variable (array or String).
Is there a way?
Thanks!

 

[SkipVought]

Hi,

Loop thru the values and load them into an array.

What have you tried so far?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Object model should give you this:

For Each pvtfld In ActiveSheet.PivotTables(1).PivotFields
    MsgBox pvtfld.Name
Next

or 

with ActiveSheet.PivotTables(1)
    for i = 1 to .PivotFields.count
        myArray(i-1) = .pivotfields(i).name
    next i
end with

If I have read your request wrong, the next level down is pivotITEMS (within a pivotFIELD)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question