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

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
59
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)?
 
Upvote 0
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. :(
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top