locked
How do I use a loop and PivotSelect? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Good day, all-

    I'm trying to loop through a pivot table to gather data for a different table.  My pivot table rows are like this:

    Person
       Activity (eg. new file, file transfer in, close file)
          Activity Detail (eg file number)

    The columns are for each quarter and a YTD.

    I'd like to loop through the pivot table, and within each person showing on the pivot table, loop through each activity listed for that person and access the data in the total column.  And if a person is not showing a given activity (eg if they have no 'close file' showing), to be able to know this is missing.

    I'm assuming I somehow do this using PivotSelect, but I'm not quite sure how to use that to select each type of field (row?) of the pivot table, and could use some advice (or thoughts on a better way to get the data from the pivot table, if a loop is not the right thing to do).

    Thanks!

    Casey

    Tuesday, September 22, 2015 3:58 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I found my answer....
    using the GETPIVOTDATA function, I can access the information I need.

        Dim rng As Range
    Set rng = Range(col_NewClaims & "8:" & col_NewClaims & "12")
    For Each cell In rng.Cells
        cell.Activate
        cell.FormulaR1C1 = "=IFERROR(GETPIVOTDATA(""File Number"",R6C1,""Activity"",""File - New Loss"",""Participant"",RC[-2]),0)"
    Next cell

    • Marked as answer by Casey_M Tuesday, September 22, 2015 5:47 PM
    Tuesday, September 22, 2015 5:46 PM