display pivot refresh date?

[Cam]

Hello,

I have an Excel file with several pivot tables in different worksheets that
I would like automate the following:
1) When the pivot tables are refreshed, it shows the date/time stamp they
were refreshed on each sheet.
2) I created a scheduler task to open the file, but need to automate it to
run the refresh all macro when it is opened once only every morning Mon to
Frid at 7AM.

I created a refresh all macro to refresh all pivot table. Thanks

 

[Barb Reinhardt]

In your refresh macro, you could add (or update) a named range/constant in
the workbook to reflect the Refresh Date/Time and then display that somewhere

 

[eric_powell]

Try using the pivot table's refreshDate property. The trick is in retrieving
the updated refreshDate when it is refeshed. Unfortunately, there are no
pivot table events (that I know of), but you can use the change event of the
respective worksheet . Your change event could call a sub like the one
below, and place the value in a nearby cell rather than a msgbox.

Sub refreshDate()
Set pvtTable = Worksheets("Yearly").PivotTables("Yearly")
dateString = Format(pvtTable.refreshDate, "Long Date")
MsgBox "The data was last refreshed on " & dateString
End Sub

 

[Patrick Molloy]

but if you refresh the pivot table this event gets fired:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

so all one would need to do is add a line like
Range("A1")="Refreshed at " & now()