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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
258
Skyybot
Skyybot
J
  • Solved
For Each Loop in Pivot Table
2
Replies
13
Views
1K
jmon
J
B
  • Question Question
dynamic formula help - supply multiple variables and process row by row
Replies
0
Views
550
BrettJH
B
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
91
HighAndWilder
H
drom
  • Solved
Pivot table count of students (counts same many times)
Replies
1
Views
903
denzo36
D

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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