I have an Exel 2007 PivotTable using classic layout that has 2 row fields. For a given data PivotCell, I want to loop through and identify the corresponding row field names and item values for that cell. My code works fine if I am on a sub-total or grand total line (0 or 1 items associated with the cell). But it fails if I try to work with a cell associated with 2 items - "Run-time error '1004': Application-defined or object-defined error".
The following line succeeds when i=1, but fails when i=2:
cellSpec(1, i) = pvtCell.RowItems(i).LabelRange.PivotCell.PivotField.SourceName
When I set a watch expression to pvtCell, I see that:
pvtCell.RowItems(2).LabelRange.PivotCell=<Application-defined or object-defined error>
pvtCell.RowItems(2).Name has the expected valid value.
Anyone know a reliable method to get the name of the RowItem's field?