none
How to Programmatically Access Rows of Data in a Pivot Table RRS feed

  • Question

  • Excel 2013.

    I have a pivot table with two rows: Group, and Member Name.  When I click the + next to Group, the Member Names expand and appear below the Group name.  I would like to programmatically process through this data, moving down the table one group at a time and accessing the member names under each group.  I DON"T want to hardcode row numbers, as they can change when the pivot table is refreshed.  So is there a nice way from VBA to process through one group at a time and reference the values in the "Member Name" rows under each group?

    Friday, August 21, 2015 5:37 PM

Answers

  • Can you turn on the Macro Recorder as you move through the steps and then view the code that's generated?  Maybe you should start there.  Then post back with specific questions.

    Also, please take a look at this site.

    http://www.contextures.com/tiptech.html

    Go down to 'Pivot Tables' and check out those samples.  You'll find lots of useful things there.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, August 22, 2015 12:45 AM
  • Hello,

    The best way (and easiest one) would be to query through SQL. So, if this is an option ...

    If not, I guess you need something like this:

       Dim strGroup As String, colMembers As Collection, ws As Worksheet, pt As PivotTable
        
        'ini
        Set ws = Worksheets("your ws")
        Set pt = ws.PivotTables(1)
        
        'go down each row
        For Each rRow In pt.DataBodyRange.Rows
        
            'new groep
            If ws.Cells(rRow.Row, 1).Value <> "" Then
                strGroup = ws.Cells(rRow.Row, 1).Value
                Set colMembers = New Collection
                
            End If
            
            Call colMembers.Add(ws.Cells(rRow.Row, 2).Value)
            
             
        Next

    Hope it helps,

    Wouter

    Monday, August 24, 2015 12:40 PM

All replies

  • Can you turn on the Macro Recorder as you move through the steps and then view the code that's generated?  Maybe you should start there.  Then post back with specific questions.

    Also, please take a look at this site.

    http://www.contextures.com/tiptech.html

    Go down to 'Pivot Tables' and check out those samples.  You'll find lots of useful things there.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, August 22, 2015 12:45 AM
  • Hello,

    The best way (and easiest one) would be to query through SQL. So, if this is an option ...

    If not, I guess you need something like this:

       Dim strGroup As String, colMembers As Collection, ws As Worksheet, pt As PivotTable
        
        'ini
        Set ws = Worksheets("your ws")
        Set pt = ws.PivotTables(1)
        
        'go down each row
        For Each rRow In pt.DataBodyRange.Rows
        
            'new groep
            If ws.Cells(rRow.Row, 1).Value <> "" Then
                strGroup = ws.Cells(rRow.Row, 1).Value
                Set colMembers = New Collection
                
            End If
            
            Call colMembers.Add(ws.Cells(rRow.Row, 2).Value)
            
             
        Next

    Hope it helps,

    Wouter

    Monday, August 24, 2015 12:40 PM