title: Get value of pivotfields for a particular cell
body: <p>I have a pivot table from ssas connection. Several pivotfields are being used in the table. For this example let's say they are Size, Color, and Location. My goal is to write some vba where I pass the cell range and get the value of the corresponding pivotfields.</p>
  
  <p>For example, If I pass the cell range for <code>B6</code> I want to know that that cell is from location2 in color2 for size1. If I pass range <code>B8</code> I get location1, Color3, Size1 etc.</p>
  
  <p><img src="https://i.stack.imgur.com/CkXHn.png" alt="enter image description here"></p>
  
  <p>Here is what I can do so far. I can get the value of the formula for that cell. Here is the result for B6: <code>GETPIVOTDATA("[Measures].[myValues]",$A$1,"[d size].[size]","[d size].[size].&amp;[Size1]","[d color].[color]","[d color].[color].&amp;[Color2]","[d location].[location]","[d location].[location].&amp;[location2]")</code> with this code:</p>
  
  <pre><code>Function xxx(ByVal xyz As Range) As Variant
    xxx = xyz.Formula
  End Function
  Sub blah()
    MsgBox (xxx(ActiveWorkbook.Sheets("Sheet1").Range("D13")))
  End Sub
  </code></pre>
  
  <p>I can also loop through the pivotfields and get their names, (but I don't know how to get their values) <code>[Measures].[myValues]</code>, <code>[d size].[size]</code> etc... with this code:</p>
  
  <pre><code>With Worksheets("Sheet1").PivotTables(1)
      For i = 1 To .PivotFields.Count
          MsgBox .PivotFields(i).Name
      Next
  End With
  </code></pre>
  
  <p>My question is now, how do I get the value of those pivotfields for a particular cell. I would like to be able to send <code>B6</code> and get back, or have access to: <code>location2</code>, <code>Color2</code>, <code>Size1</code></p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Getting closer... I can now loop through each field and get their names and values like this <code>[d size].[size]&amp;[Size1]</code> etc, but now I want to be able to get just the value of <code>Size1</code> or <code>[Size1]</code>:</p>
  
  <pre><code>Sub Test()
    Dim iRange As Variant
    iRange = ActiveCell.Address
    FieldPicker (iRange)
  End Sub
  
  Function FieldPicker(targetCell As Variant) As Variant
    With Range(targetCell).PivotCell
        For i = 1 To .RowItems.Count
            MsgBox .RowItems(i).SourceName
        Next
    End With
  End Function
  </code></pre>