Refresh Pivot Tables on Worksheet Activate IF a Change Has Been Made

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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.

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!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,095
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you double click on worksheet name at VBA window (Left pane) and then select worksheet and activate from right pane (above of code writing section)?
 

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Are you double click on worksheet name at VBA window (Left pane) and then select worksheet and activate from right pane (above of code writing section)?
Yep, that's what I did. :(
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,095
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. I think you change worksheet change event to worksheet activate event because worksheet activate don't have ByVal Target As Range.
2. you must set also your target cells.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,095
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think you can refresh Pivot tables only when a worksheet activate and don't need to use change event for table range.
and at this situation , You can go to sheet tab name and right click and then select worksheet and activate from dropdown menu at VBA windows and input this code:
VBA Code:
Private Sub Worksheet_Activate()
    Application.EnableEvents = False
    Dim pt As PivotTable
     For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
     Next pt
   Application.EnableEvents = True
End Sub
 

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Thanks, but it looks like that is still refreshing every time it is activated. I don't want it to refresh every time because the refresh is slow. I only want it to refresh when that sheet is activated if the source data has changed.

For example, I have the Dashvoard sheet set up to activate when the workbook opens, so regardless the user always sees the Dashboard first. However, I don't want to refresh the PIVOT tables when it opens because it slows down the opening process even though nothing has been changed.

Or, in a situation where the user clicks away from the Dashboard sheet to look at something else, when they go back to the Dashboard, everything updates even though changes have not been made.

Does that make sense?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,087
Messages
5,576,035
Members
412,694
Latest member
Deaf1Too
Top