Results 1 to 3 of 3

Pivot Tables: Loop thru RowFields within RowFields

Threaded View

  1. #1
    Registered User
    Join Date
    03-04-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Pivot Tables: Loop thru RowFields within RowFields

    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
    Attached Files Attached Files
    Last edited by davidjones; 03-06-2012 at 09:22 PM. Reason: Problem Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1