Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with pivot tables items
Hello all,
I am hoping someone can help. I am trying to programmatically work with data in pivot tables but am having some difficulty looping through items in the table in an efficient way. Here's a simplified version of what I am trying to do: Consider a Pivot table that contains two row items (Group and SubGroup) and one data item (Data): Group1 SubGroup1 Data1 Group1 SubGroup2 Data2 Group1 SubGroup3 Data3 Group2 SubGroup4 Data4 Group2 SubGroup5 Data5 Group2 SubGroup6 Data6 I would like to find an efficient way to loop through each of the Groups, and within each of the Groups through the SubGroups. The goal is to get each Group, SubGroup and Data combination and copy the values for each to another range depending on the Group. I have tried the following so far: Reference ChildItems: unfortunately this does not work like I hoped. For example, I was hoping that SubGroups1, 2 and 3 would be ChildItems of Group1, but they apparently are not (I tried looping through all of the PivotItems to count ChildItems but none have any). Next, I thought I could just loop through the Group items, and set visibility for all but one at a time to False. Then, I could loop through all visible SubGroup items and return the corresponding Data items. However, if I set Group2's visibility to False, although the pivot table no longer shows the values for SubGroups 4, 5 and 6, their visibility is unfortunately technically not False. I hope I've been clear. If anyone has any suggestions I would greatly appreciate hearing them. Thanks in advance. Best, TP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating pivot items on 3 pivot tables contained on one sheet | Excel Programming | |||
Updating pivot items in three pivot tables on one sheet via VBA | Excel Programming | |||
Name items in Pivot Tables? | Excel Discussion (Misc queries) | |||
Cycle through Pivot Tables with Pivot Items | Excel Programming | |||
ticked items on Pivot tables | Excel Discussion (Misc queries) |