Solved: Obtain Pivot Table Cell properties with VBA

[JimS]

Is there a way for VBA to obtain the data values that are associated with a particular cell in a Pivot Table (all the Row Information for a cell)?

If I try to use the ActiveCell (Row, Column -1) it returns a "blank" (in the attached example) since that cell is blank in the Pivot Table.

Thanks for any help...

JimS

[Paul_Hossler]

Not a lot of error checking

I don't think there's a PT equivalent of 'Trace Dependents'


Option Explicit

Sub test()

With ActiveSheet
MsgBox "Jane mgr = " & Manager(.Range("C4"))
MsgBox "John mgr = " & Manager(.Range("C5"))
MsgBox "Kim mgr = " & Manager(.Range("C6"))
MsgBox "Time mgr = " & Manager(.Range("C7"))
End With

End Sub
Function Manager(emp As Range) As String

With emp.Cells(1, 1)
If Len(.Offset(0, -1).Value) > 0 Then
Manager = .Offset(0, -1).Value
Else
Manager = .Offset(0, -1).End(xlUp).Value
End If
End With
End Function


Paul

[SamT]

Take a look at the PivotField Object and it's Properties.

With my total lack of experience with Pivot Tables, I recommend the DataRange Property.

[Aflatoon]

This may get you started:
Function PivotInfo(rInput As Range) As String
Dim pCell As Excel.PivotCell
Dim pf As Excel.PivotField
Dim pi As Excel.PivotItem
Dim sOut As String

On Error Resume Next
Set pCell = rInput.PivotCell
On Error GoTo err_handle

If pCell Is Nothing Then
PivotInfo = "Not a pivot cell"
Else
Select Case pCell.PivotCellType
Case xlPivotCellValue 'Any cell in the data area (except a blank row).
If pCell.RowItems.Count Then
sOut = "Row items: " & vbLf
For Each pi In pCell.RowItems
sOut = sOut & pi.Parent.Name & ": " & pi.Value & vbLf
Next pi
End If
If pCell.ColumnItems.Count Then
sOut = sOut & "Column items: " & vbLf
For Each pi In pCell.ColumnItems
sOut = sOut & vbLf & pi.Parent.Name & ": " & pi.Value
Next pi
End If
sOut = sOut & pCell.PivotField.Name
Case Else
sOut = "Not a pivot data cell"
End Select
End If
PivotInfo = sOut
Exit Function

err_handle:
PivotInfo = "Unknown error"
End Function

[JimS]

Thanks - both of you.

I was able to get what I needed using Pauls recommendation.

JimS