T
todd.phin
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
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