Good Afternoon,
I want to refresh all pivot tables in a workbook when a worksheet (Dashboard)is activated. However, there are many pivot tables and it takes a long time, so I only want it to happen if the source data (Roll_Up_Table) has changed.
I pulled from a few different threads and came up with this. However, it isn't working.
Any ideas would be appreciated.
Thanks in advance!
I want to refresh all pivot tables in a workbook when a worksheet (Dashboard)is activated. However, there are many pivot tables and it takes a long time, so I only want it to happen if the source data (Roll_Up_Table) has changed.
I pulled from a few different threads and came up with this. However, it isn't working.
Any ideas would be appreciated.
VBA Code:
Private Sub Worksheet_Activate(ByVal Target As Range)
Application.EnableEvents = False
Dim KeyCells As Range
Set KeyCells = Range("Roll_Up_Table")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Dim pt As PivotTable
For Each pt In Me.PivotTables
pt.RefreshTable
Next pt
Application.EnableEvents = True
End If
End Sub
Thanks in advance!