Loop through each row in a Pivot Table

P

PMCODER

New Member
Joined
May 9, 2017
Messages
1
Hello all,

I tried searching the forums and cannot figure this out. I am new to VBA but have experience with C and PHP.

I have a Pivot Table grouped by State & Name in one worksheet and all other data spread across other worksheets.

A sample of the Pivot table data is below:
[TABLE="width: 1118"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Shoes[/TD]
[TD]Sum of Shoes - Void[/TD]
[TD]Sum of Boots[/TD]
[TD]Sum of Boots - Void[/TD]
[TD]Sum of Socks[/TD]
[TD]Sum of Socks - Void[/TD]
[TD]Sum of Total[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]3415[/TD]
[TD]443[/TD]
[TD]487[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]4366[/TD]
[/TR]
[TR]
[TD]John A. Smith[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]493[/TD]
[TD]54[/TD]
[TD]44[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]595[/TD]
[/TR]
[TR]
[TD]James Bond[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]77[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]97[/TD]
[/TR]
[TR]
[TD]User: Smith12, John[/TD]
[TD]72[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]Baba Dook[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]3329[/TD]
[TD]279[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]664[/TD]
[TD]51[/TD]
[TD]4324[/TD]
[/TR]
[TR]
[TD]Dane Great[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Greet V. Bob[/TD]
[TD]332[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]122[/TD]
[TD]5[/TD]
[TD]483[/TD]
[/TR]
[TR]
[TD]User: Smith16, John[/TD]
[TD]43[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]68[/TD]
[/TR]
</tbody>[/TABLE]

---------------------

I am attempting to loop through each state in the pivot table to:
1. Retrieve state level data from another worksheet and copy to the top row for the state. Will do this once for each state.

2. Loop through all employees for that specific state and retrieve employee level data. The challenges here are the employee names are stored in full name format in HR and other systems (<last name="">, <first name=""> <middle name=""> etc) so I have to check the employee last name for the state and copy only the matching data to the destination worksheet.

Below is a desired output based on the above:
[TABLE="width: 1440"]
<tbody>[TR]
[TD][TABLE="width: 624"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Shoes[/TD]
[TD]Sum of Shoes - Void[/TD]
[TD]Sum of Boots[/TD]
[TD]Sum of Boots - Void[/TD]
[TD]Sum of Socks[/TD]
[TD]Sum of Socks - Void[/TD]
[TD]Sum of Total[/TD]
[TD]Shoes
External Count[/TD]
[TD]Boots
External Count[/TD]
[TD]Socks
External Count[/TD]
[TD]Sum of External Count[/TD]
[TD]Emp Response Count[/TD]
[TD]Emp Void Count[/TD]
[TD]Emp Commission Rate[/TD]
[TD]Other Emp Data4[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]3415[/TD]
[TD]443[/TD]
[TD]487[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]4366[/TD]
[TD]3867[/TD]
[TD]500[/TD]
[TD]9[/TD]
[TD]4376[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John A. Smith[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]493[/TD]
[TD]54[/TD]
[TD]44[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]595[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD]James Bond[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]77[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]97[/TD]
[TD]87[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User: Smith12, John[/TD]
[TD]72[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD]Baba Dook[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]3329[/TD]
[TD]279[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]664[/TD]
[TD]51[/TD]
[TD]4324[/TD]
[TD]3611[/TD]
[TD]1[/TD]
[TD]718[/TD]
[TD]4330[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dane Great[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD]Greet V. Bob[/TD]
[TD]332[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]122[/TD]
[TD]5[/TD]
[TD]483[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD]User: Smith16, John[/TD]
[TD]43[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]68[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX * YYY[/TD]
[TD]XXX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can you please give ideas on how to code this?

Here is the code that I used to loop through the elements and is not working as expected:

Set stateCode = pvt.pivotFields("STATE_CODE")
Set acoName = pvt.pivotFields("ACO_NAME")

For Each statePivotItem In stateCode.PivotItems
stateCode.ClearAllFilters
stateCode.CurrentPage = statePivotItem.Name

MsgBox "State is " & statePivotItem.Value 'Works well

'Copy all state level data to the appropriate cells

For Each itm In fld.PivotItems
​
MsgBox "Item is " & itm.Value 'Displays ALL employees not just this state's employees

'Do other stuff to copy other data based on ACO Name & State code​
Next​
Next


Many thanks!</middle></first></last>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
315
Skyybot
Skyybot
J
  • Solved
For Each Loop in Pivot Table
2
Replies
13
Views
2K
jmon
J
B
  • Question Question
dynamic formula help - supply multiple variables and process row by row
Replies
0
Views
575
BrettJH
B
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
102
HighAndWilder
H
drom
  • Solved
Pivot table count of students (counts same many times)
Replies
1
Views
943
denzo36
D

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top