Store List of ChildItems from OLAP pivot table in an Array

[hrm1220]

I have several worksheets with a pivot table on each worksheet from OLAP source in Excel 2007.

The goal is to get the group1 (Parent), group2 (Parent child),and group3 (child).
example:
Group1 (the variant [purple]VarField (1,x)[/purple]) Mammal
Group2 (the variant [purple]VarField (2,x)[/purple]) Dog
Group3 (the variant [purple]VarField (3,x)[/purple]) German Shepard


I've tried several times to try to get a list stored in an array, but doesn't seem to work.

Dim pvt As PivotTable, PI As PivotItemList
Dim ws As Worksheet, [purple]varField[/purple] as Variant
Dim pitem As PivotItem, ch


ReDim varField(3, x)
For Each ws In ActiveWorkbook.Worksheets
For Each pvt In ws.PivotTables
Application.Calculation = xlCalculationManual
x=0
 With pvt.PivotFields("[Animal].[Parent Child Animal Group].[Type]")

    For Each pitem In .PivotItems
        For Each ch In pvt.PivotFields("[Animal].[Parent Child Animal Group].[Type]").PivotItems(pitem).ChildItems
        
    

    ReDim varField(3, x)
   varField(1, x - 1) = .PivotItems(pitem).Value
   varField(2, x - 1) = .PivotItems(ch).Value
   varField(3, x - 1) = .PivotItems(x).Value
  x=x+1
        
        Next ch
    Next pitem
  End With
Next pvt
Next ws

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

...but doesn't seem to work

What does that mean, specifically?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}