VBA To Determine Row label reference values in Pivot Table

[LucasLondon]

Hi,

I would like to indentify the row references in a pivot table to which an actual value relates. Is there an easy way to do this?

For example in the attached workbook Pivot example, the maximum Value (24) occurs in cell B77. How Can I automatically extract the Day, Time of Day, Content that this data value relates to in the pivot table row labels. S

o in this case I want a msg box (or output somewhere on the sheet) would say:

Day = Saturday,
Time of Day = Afternoon
Content = RJ.

Is this something easy to do?

Many Thanks

Lucas

[mancubus]

hi.
this seems to be what you are after.


function lifted from: http://www.mrexcel.com/forum/excel-questions/332678-pivottable-problem.html



Sub TestPivotInfo()

Dim MaxRange As Range, MaxCell As Range

Set MaxRange = ActiveSheet.PivotTables("PivotTable1").PivotFields("Average of nooftransactions").DataRange
Set MaxCell = MaxRange.Find(Application.Max(MaxRange))

MsgBox PivotInfo(MaxCell)

End Sub





Function PivotInfo(rngInput As Range) As String
'http://www.mrexcel.com/forum/excel-questions/332678-pivottable-problem.html#post1638552

Dim pc As PivotCell
Dim pf As PivotField, pi As PivotItem
Dim strOut As String

On Error Resume Next
Set pc = rngInput.PivotCell
On Error GoTo err_handle

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

err_handle:
PivotInfo = "Unknown error"

End Function

[LucasLondon]

Thanks very much,

This seems to do the job.

Cheers,

Lucas




hi.
this seems to be what you are after.


function lifted from: http://www.mrexcel.com/forum/excel-questions/332678-pivottable-problem.html



Sub TestPivotInfo()

Dim MaxRange As Range, MaxCell As Range

Set MaxRange = ActiveSheet.PivotTables("PivotTable1").PivotFields("Average of nooftransactions").DataRange
Set MaxCell = MaxRange.Find(Application.Max(MaxRange))

MsgBox PivotInfo(MaxCell)

End Sub





Function PivotInfo(rngInput As Range) As String
'http://www.mrexcel.com/forum/excel-questions/332678-pivottable-problem.html#post1638552

Dim pc As PivotCell
Dim pf As PivotField, pi As PivotItem
Dim strOut As String

On Error Resume Next
Set pc = rngInput.PivotCell
On Error GoTo err_handle

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

err_handle:
PivotInfo = "Unknown error"

End Function