Pulling Data From PivotTable

[DtTall]

I am writing a macro to extract data from a PivotField which is part
of my PivotTable (included) and the corresponding data from a
PivotField which is only in the PivotTable Field list (non-included).
I have not been able to get the data points from the two fields to
match up as I have only been able to get the data from the second (non-
included) field in alphabetical order.

The data as I need it would look like this....

Included field: Apple, Banana, Pear
Non-included field: Red, Yellow, Green

..... but what I'm getting is this.....

Included field: Apple, Banana, Pear
Non-included field: Green, Red, Yellow

The problem is further complicated when I am pulling only visible
items from the PivotTable, (i.e. Apple and Pear when Banana is hidden)
where non-included items do not distinguish between visible and
hidden.

The data is in an external data source (a database) and so am looking
to pull it in though the PivotTable.

Below is my macro that pulls the data back incorrectly.

Set pTbl = ThisWorkbook.Sheets(1).PivotTables(1)
rw = 0
For Each frt In pTbl.PivotFields("Fruit").PivotItems
rw = rw + 1
With Sheets("Sheet2")
..Cells(rw, 1).Value = frt.Name
..Cells(rw, 2).Value = pTbl.PivotFields("Color").PivotItems(rw).Name
End With
Next

 

[barnabel]

Several things come to mind...

Is there a reason you are not putting the color field as a pivot row too?

There is not correlation between the order of the items in separate pivot
fields. If you have sorting turned on for a field then the items will be in
sorted order reguardless of where they appear in the data.

Since you are just indexing through the fruit pivot item and not checking
the value of hidden you are processing it even if it isn't displayed.

I would add color to the pivot table and then index through the cells on the
sheet rather than the items in the pivot field.

Peter