How do I use a loop and PivotSelect? RRS feed

  • Question

  • 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:

       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).



    Tuesday, September 22, 2015 3:58 PM


  • 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.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