Calling PivotTable.RefreshTable fires a Workbook_SheetPivotTableUpdate event for each pivot table on the worksheet

Question

In my workbook I want to show when a pivot table was last refreshed. I have code to refresh all pivot tables but also want to track when the user manually refreshes a pivot table.

To refresh all tables in code I am looping through all the worksheets in a workbook looking for those that have pivot tables. The code then calls RefreshTable on each pivot table in that sheet.

    For Each wks In theDataWorkbook.Worksheets
        theSheetName = wks.Name
        If wks.PivotTables.Count > 0 Then
            Debug.Print PROC_NAME & ": " & theSheetName
            For i = 1 To wks.PivotTables.Count
                wks.PivotTables(i).RefreshTable
            Next i
        End If
    Next wks

I'm using the workbook event to respond when a pivot table is refreshed and then write a timestamp to the worksheet.

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Debug.Print Sh.Name & " " & Target.Name
    UpdatePivotTableRefreshedTimestamp(Target) ' This writes a timestamp to the sheet
End Sub

The workbook event is firing for each pivot table on the sheet even though only one has been updated. The event even fires for a different pivot table on another sheet. Why is this? The pivot tables uses the same Excel Data Table as their source.

@Rory - so would it be simpler to refresh/update the cache directly (if that is possible) or does that not feed through to the worksheets? — ChipsLetten

sn152 sn152 · Accepted Answer · 2016-09-22T12:39:37

This may not satisfy your requirement fully but to some extend. Check out the below code that I used to check who has updated the workbook and when was it updated, which sheet was updated. In my workbook I had a separate sheet named "Log" which is used to capture these data. You can modify it according to your need.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Value = Environ("UserName")
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = "'" & Target.Formula
.Offset(1, 4) = Previous
Previous = ""
.Offset(1, 5) = Now
End With
Application.EnableEvents = True
End Sub

Calling PivotTable.RefreshTable fires a Workbook_SheetPivotTableUpdate event for each pivot table on the worksheet

1 Answers