Last Refresh date for pivot table

[HRobertson]

Good morning. I have a spreadsheet that contains a pivot table and am
wondering if there is a formula that can be used at the top of the
spreadsheet to provide the last date the pivot table was refreshed? Any help
is much appreciated.

Thanks

 

[Lori]

Select a cell in the pivot table then Choose tools>macros>visual basic editor
[alt+f11] and type in the immediate window:
Activecell.PivotTable.RefreshDate
followed by enter.

 

[Ron Coderre]

Perhaps this User Defined Function (UDF):

Hold down the [ALT] key and press [F11]
....(to see the Visual Basic Editor)

Locate your workbook, in the left window list
Right-Click on it and select: Insert Module

Make sure the first line at
the top of that module is: Option Explicit

Then copy the below UDF code and paste it into that window
(anywhere under: Option Explicit)

Public Function LastPvtUpdate(rngCell As Range) As Variant
Dim cPvtCell As Range
Dim pvtTbl As PivotTable
On Error Resume Next
Set pvtTbl = rngCell.Cells(1, 1).PivotTable
If Err.Number = 0 Then
With pvtTbl
LastPvtUpdate = .RefreshDate
End With
Else
LastPvtUpdate = "Error: No Pivot Table Reference!"
End If
End Function

Now switch to the worksheet that has the Pivot Table.

Assuming your pivot table begins in cell B10...

This formula will return the last update date/time stamp
for the referenced pivot table data:
B9: =LastPvtUpdate(B10)

Format that cell as date/time
<format><cells><number tab>
Category: Time
Type: (select an appropriate date/time format)

OR....you could use something like this:
="Last refreshed: "&TEXT(LastPvtUpdate(B10),"m/d/yy h:mm AM/PM")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)