Loop through all elements in Pivot Table

Hello,

I have a Pivot Table grouped by State abbreviation & Employee Name in one worksheet.

The code needs to add state level data and employee level data from other worksheets to each row in this pivot table. Essentially the goals are:

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. Copy parts of the external data (counts, employee hours, hourly rate etc) and paste in the employee's row.

-----------

The initial code is below:

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


'This does not work as it loops through ALL employees instead of only this state's employees

For Each itm In fld.PivotItems

MsgBox "Item is " & itm.Value

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

Next

Next

-----------

What code should be used to loop and read each element in the Pivot table? The number of rows will be variable depending on the employees but the columns will remain the same.

Thanks!

Frank

Bump.

Any ideas on how to loop through each row in a Pivot table with two key fields and read specific elements?

Thank you.