display pivot refresh date?




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


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()

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question