display pivot refresh date?

C

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
 
B

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
 
E

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
 
P

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

Top