I have a pivot table that analyzes how salespeople have performed within different cities.
I am trying to write a macro that will do some comparisons based on each city that the salesperson has gone to. As part of the macro, I loop through each sales person, and nested within that loop, I attempt to loop through each city. however, when I try to do the nested loop by:
For Each city In pvt.RowFields("City").PivotItems()
it loops through each possible city, instead of just the cities associated with that salesperson.
Note: I am showing a simplified version so it is easier to understand. Because the actual amount of data is very large, I need to keep the pivot table set up as rows within rows, I can't move salespeople or cities into columns.
I've attached the workbook and a screenshot.
Example.jpg
Thanks in advance!
Sub AnalyzeSales()
'Initialize Variables
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Method: Loop through each group in the pivot table and:
'1. Extract the subtotal for a salesperson
'2. Determine if any city is less than 500 or greater than 2000
For Each sPerson In pvt.RowFields("SalesPerson").PivotItems()
'Next part
'Debug.Print sPerson
For Each city In pvt.RowFields("City").PivotItems()
Debug.Print sPerson & ": " & city
Next city
'Debug.Print Salesman & " :" & totalSales
Next sPerson
End Sub
Bookmarks