Programmatically getting a Max value from a PivotTable
 

Is it possible to extract the maximum value of a pivot table field
without going to the raw data that the table is based on? .... I assume
that this will include caches: not something I've ever messed around
with.

Any help greatly appreciated
Jason.

Programmatically getting a Max value from a PivotTable
 

Jason,

Something like this, where you want the max value in the "Total" column:

Sub FindMAX()
Dim myPT As Range
Dim myCell As Range

Set myPT = Range("A3").CurrentRegion
Set myCell = myPT.Find("Total", , , xlWhole)

MsgBox Application.Max(Intersect(myCell.EntireColumn, myPT))

End Sub

But if you need to leave out the Grand Total at the bottom, you would need to do something like

Sub FindMAX()
Dim myPT As Range
Dim myCell As Range

Set myPT = Range("A3").CurrentRegion
Set myPT = myPT.Resize(myPT.Rows.Count - 1)
Set myCell = myPT.Find("Total", , , xlWhole)

MsgBox Application.Max(Intersect(myCell.EntireColumn, myPT))

End Sub


HTH,
Bernie
MS Excel MVP


"WhytheQ" wrote in message
oups.com...
Is it possible to extract the maximum value of a pivot table field
without going to the raw data that the table is based on? .... I assume
that this will include caches: not something I've ever messed around
with.

Any help greatly appreciated
Jason.