Pivot Table - loop through Pivot Item children

[mikekingtx]

I'm a .Net developer and it's obvious I'm not grasping the Pivot Table correctly. I've written code to create a sum of values based on a worksheet. Specifically:<br>Location<br>ID<br>Dept<br>Sum of Hours Worked. Location, ID and Dept are rows. This effectively provides the aggregate values that I need based on the row groupings.<br><br>Here's where this is falling apart. I need to create a new worksheet based on these values. I assumed the three row values - Location, ID and dept - would be in a hierarchy. It's possible they are, I just can't figure out the object model.<br><br>When I loop through the PivotItems collection of the PivotFields("Location"), I get what I need. However, I'm unable to determine how to loop through the child values (just for that location). PivotFields("ID") returns all IDs. I can't figure out how to return only the child entries for each pivot item. GetPivotData hasn't been very useful for this. As far as I can tell, GetPivotData, while its return type is listed as Range, throws an error when more than one cell is returned. Worst case, I suppose I can just parse the data in the DataBodyRange of the pivot table - maybe not, I haven't tried that. I'm hoping there's a way to iterate through these collections, but based on what I've seen from Google searches, there may not be. Does my pivot table need to be rearranged? I suppose I could also just dump this data into a data table - that may be my final solution, although I'm not sure how much support VBA has for that. Unfortunately, at least for me anyway, this data must remain in Excel.<br>Thanks in advanced for any advice.<br>-- Mike

 

[mikekingtx]

After some testing, I figured out why GetPivotData wasn't working - RowFields are not DataFields. I'm just going to stick with what I know and use a RecordSet. If anybody can provide any insight to how this should have been done, I'd love to know how. Thanks.

 

[tjeffryes]

To get the child items of a pivot item, just iterate through the pivot items' DataRange:

Set oPivotField = oPivot.PivotFields("commodity")
For Each oPivotItem In oPivotField.PivotItems
For Each rngCell In oPivotItem.DataRange.Cells
Debug.Print rngCell.Text
Next rngCell
Next oPivotItem

 

[SpillerBD]

You can probably rearrange the PivotTable or rather a copy. I am not sure your aim, but Pivot Table can reproduce the original table when you use Tabular mode, show no Subtotals or Grand totals and use the Repeat Row Labels.

If you think of the fields as answering the questions you have, you'll get there.