Loop through each row in a Pivot Table

[PMCODER]

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:

Row Labels	Sum of Shoes	Sum of Shoes - Void	Sum of Boots	Sum of Boots - Void	Sum of Socks	Sum of Socks - Void	Sum of Total
AL	3415	443	487	12	8	1	4366
John A. Smith	7	1	0	0	0	0	8
John Smith	493	54	44	1	3	0	595
James Bond	10	2	0	0	0	0	12
AR	77	10	8	2	0	0	97
User: Smith12, John	72	10	8	2	0	0	92
Baba Dook	5	0	0	0	0	0	5
AZ	3329	279	0	1	664	51	4324
Dane Great	6	0	0	0	0	0	6
Greet V. Bob	332	24	0	0	122	5	483
User: Smith16, John	43	18	0	0	7	0	68

<tbody>
</tbody>

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

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:

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

<tbody>
</tbody>

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>