View Full Version : Solved: Obtain Pivot Table Cell properties with VBA
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
03-12-2013, 02:52 PM
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
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
03-13-2013, 05:35 AM
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
Thanks - both of you.
I was able to get what I needed using Pauls recommendation.
JimS
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.