Pivot Table - loop through Pivot Item children

mikekingtx

New Member
Joined
Jan 7, 2014
Messages
2
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mikekingtx

New Member
Joined
Jan 7, 2014
Messages
2
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

New Member
Joined
Aug 29, 2017
Messages
1
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top