Date Stamp whenever someone refreshes the table

Question

I have a pivot table in my Sheet1 connected to external sources which is also an excel sheet. All i am trying to do is to get a date and time stamp whenever someone refreshes pivot table.

I get an error Object doesn't support this property or method.

Private Sub Refresh_Click()
Dim PT As PivotTable
Dim WS As Worksheet
  For Each WS In ThisWorkbook.Worksheets
  For Each PT In WS.PivotTables
  PT.RefreshTable
  WS.Sheets("Month-to-Date").Range("P5") = TimeValue(Now)
  Next PT
  Next WS
End Sub

Try: `ThisWorkbook.Sheets("Month-to-Date").Range("P5") = TimeValue(Now)` — cybernetic.nomad, Apr 01 '19 at 19:28
Just something to think about. What happens if someone changes the name of the worksheet "Month-to-Date" to something else by accident, or on purpose. Wouldnt that throw the entire code off? — Zack E, Apr 01 '19 at 21:25

score 1 · Accepted Answer · answered Apr 01 '19 at 19:59

1

The problem is that WS is a Worksheet and a Worksheet does not support another Worksheet as a property.

Worksheets are properties of Workbooks so

ThisWorkbook.Sheets("Month-to-Date").Range("P5") = TimeValue(Now)

Fixes the problem

answered Apr 01 '19 at 19:59

cybernetic.nomad

6,100
3
18
31

score 0 · Answer 2 · answered Apr 01 '19 at 23:10

There is a fancy built-in event in Excel VBA for this. In the worksheet of the pivot table, select the event PivotTableUpdate (Event Documentation):

As far as probably there would be more than 1 pivot tables on the worksheet, it is a good idea to check which one is the refreshed one, using its name:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Select Case Target.Name

    Case "PivotTable2"
        Debug.Print "Write date somewhere "; Format(Now, "DD-MM-YYYY")
    Case Else
        Debug.Print "Not PivotTable2"
    End Select        

End Sub

"DateStamp" is obtained through Format(Now, "DD-MM-YYYY") and printed to the immediate window (Ctrl+G). Instead of printing there, it is a possible option to write it to a server or to a separate worksheet, getting the last cell of a row like this:

Case "PivotTable2"
    Worksheets(2).Cells(lastCell, 1) = Format(Now, "DD-MM-YYYY")

Date Stamp whenever someone refreshes the table

2 Answers2