Solution: Refresh All Code | Pivot Tables, Charts, Formulas, Forms & Link Sources

DrHacker

New Member
Joined
Jun 4, 2018
Messages
31
Hello everyone, here is a powerful solution for who needs to update All at once (Pivot Tables, Charts, Formulas, Forms & Link Sources) with a single Macro.

It requires Access Reference as part of the code, for that:

Alt+F11 > Tools > References > Microsoft Access

Code its divided by two sections at the same module (Module name: Functions - It can be changed if prefer)

Section One - Optimize Code Speed (Optional)
VBA Code:
Public Sub OptimizeCodeSpeed()

  On Error Resume Next

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableAnimations = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
         
  On Error GoTo 0
                   
End Sub

Public Sub OptimizeCodeSpeedRestore()

  On Error Resume Next

    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic

  On Error GoTo 0

End Sub

Section Two - Refresh All Macro

VBA Code:
Sub RefreshAll() 'Refresh All Workbook Connections, Pivot Tables, Charts, Forms and Formulas
   
    Dim conn As Variant
    Dim pvtTbl As PivotTable
    Dim pCache As PivotCache
    Dim myChart As ChartObject
    Dim obj As AccessObject
    Dim dbs As Object
    Dim intFormCount As Integer
   
    On Error GoTo ErrorHandler
   
    Call Functions.OptimizeCodeSpeed
   
    ActiveWorkbook.RefreshAll
   
    'Connections Refresh
        Application.CalculateUntilAsyncQueriesDone
        Application.CalculateFullRebuild
        Application.CalculateUntilAsyncQueriesDone
             
        For Each conn In ActiveWorkbook.Connections
           
            conn.ODBCConnection.BackgroundQuery = False
       
        Next conn

    'Refresh all pivot tables
        For Each pCache In ActiveWorkbook.PivotCaches
            pCache.Refresh
        Next pCache
   
        For Each pvtTbl In ActiveSheet.PivotTables
            pvtTbl.RefreshTable
        Next
       
    'Refresh all Workbook Charts
        For Each myChart In ActiveSheet.ChartObjects
            myChart.Chart.Refresh
        Next myChart
       
'Refresh Access Forms, it requieres: Tools > References > Microsoft Access
        Set dbs = Application.CurrentProject
        intFormCount = dbs.AllForms.Count - 1
   
        For i = 0 To intFormCount
            If dbs.AllForms(i).isloaded = True Then
                dbs.AllForms(i).Refresh
            End If
        Next
       
    'Refresh Workbook Links Sources
        ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
   
    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

ErrorHandler:

    Call Functions.OptimizeCodeSpeedRestore

    Exit Sub

End Sub
 

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

richh

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016
Hi DrHacker,

Thanks for the code! Does it work on refreshing queries as well? That will be super helpful for my weekly reports.
 

DrHacker

New Member
Joined
Jun 4, 2018
Messages
31
Hi DrHacker,

Thanks for the code! Does it work on refreshing queries as well? That will be super helpful for my weekly reports.


I hope yes, they’re included as part of the macro:


VBA Code:
Application.CalculateUntilAsyncQueriesDone

Application.CalculateFullRebuild

Application.CalculateUntilAsyncQueriesDone



For Each conn In ActiveWorkbook.Connections

conn.ODBCConnection.BackgroundQuery = False

Next conn
 

pvkvimalan

New Member
Joined
Dec 19, 2017
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello everyone, here is a powerful solution for who needs to update All at once (Pivot Tables, Charts, Formulas, Forms & Link Sources) with a single Macro.

It requires Access Reference as part of the code, for that:

Alt+F11 > Tools > References > Microsoft Access

Code its divided by two sections at the same module (Module name: Functions - It can be changed if prefer)

Section One - Optimize Code Speed (Optional)
VBA Code:
Public Sub OptimizeCodeSpeed()

  On Error Resume Next

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableAnimations = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
        
  On Error GoTo 0
                  
End Sub

Public Sub OptimizeCodeSpeedRestore()

  On Error Resume Next

    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic

  On Error GoTo 0

End Sub

Section Two - Refresh All Macro

VBA Code:
Sub RefreshAll() 'Refresh All Workbook Connections, Pivot Tables, Charts, Forms and Formulas
  
    Dim conn As Variant
    Dim pvtTbl As PivotTable
    Dim pCache As PivotCache
    Dim myChart As ChartObject
    Dim obj As AccessObject
    Dim dbs As Object
    Dim intFormCount As Integer
  
    On Error GoTo ErrorHandler
  
    Call Functions.OptimizeCodeSpeed
  
    ActiveWorkbook.RefreshAll
  
    'Connections Refresh
        Application.CalculateUntilAsyncQueriesDone
        Application.CalculateFullRebuild
        Application.CalculateUntilAsyncQueriesDone
            
        For Each conn In ActiveWorkbook.Connections
          
            conn.ODBCConnection.BackgroundQuery = False
      
        Next conn

    'Refresh all pivot tables
        For Each pCache In ActiveWorkbook.PivotCaches
            pCache.Refresh
        Next pCache
  
        For Each pvtTbl In ActiveSheet.PivotTables
            pvtTbl.RefreshTable
        Next
      
    'Refresh all Workbook Charts
        For Each myChart In ActiveSheet.ChartObjects
            myChart.Chart.Refresh
        Next myChart
      
'Refresh Access Forms, it requieres: Tools > References > Microsoft Access
        Set dbs = Application.CurrentProject
        intFormCount = dbs.AllForms.Count - 1
  
        For i = 0 To intFormCount
            If dbs.AllForms(i).isloaded = True Then
                dbs.AllForms(i).Refresh
            End If
        Next
      
    'Refresh Workbook Links Sources
        ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
  
    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

ErrorHandler:

    Call Functions.OptimizeCodeSpeedRestore

    Exit Sub

End Sub
Dear @DrHacker

I recently tried using to code to update a Pivot table. I followed the procedure you mentioned.

1. Checked the Microsoft Access in the Reference section
2. Pasted both the codes in a single module
3. Executed the coed.

I was however getting an error (Images attached).

Please help me in finding a solution.
 

Attachments

  • Excel VBA Error-1.JPG
    Excel VBA Error-1.JPG
    55.9 KB · Views: 9
  • Excel VBA Error-2.JPG
    Excel VBA Error-2.JPG
    91 KB · Views: 8

DrHacker

New Member
Joined
Jun 4, 2018
Messages
31

ADVERTISEMENT

Dear @DrHacker

I recently tried using to code to update a Pivot table. I followed the procedure you mentioned.

1. Checked the Microsoft Access in the Reference section
2. Pasted both the codes in a single module
3. Executed the coed.

I was however getting an error (Images attached).

Please help me in finding a solution.

To solve the problem you have two ways to do it:

First Option:

Rename your module name from "Module1" to "Functions". To do it: Select module and press "F4"

Second Option:


At VBA code, replace all: "Call Functions." with "Call Module1."

If you have any additional question, please let me know. Ill try to answer you!
 

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi
In your optimize speed

Application.EnableAnimations = False
Only appears in the first part, is that the default

ActiveSheet.DisplayPageBreaks = False
Appears in both parts as False.
How come enableanimations doesn't appear in both parts

Also, does the call exit sub need to appear before the error handler or doesn't it matter i.e.

VBA Code:
    Call Functions.OptimizeCodeSpeedRestore
Exit sub

ErrorHandler:

    Call Functions.OptimizeCodeSpeedRestore

End sub
 
Last edited:

DrHacker

New Member
Joined
Jun 4, 2018
Messages
31

ADVERTISEMENT

I’ll try to answer your questions as clear as possible:

Application.EnableAnimations = False
I don’t like the animations, i feel like it slows down Excel. You can add this line to restore if you want and change it to True.


ActiveSheet.DisplayPageBreaks = False
If you change it to True when restore, they will be always visible. You can comment the line or eliminate if not needed.

Why its not in both parts?
For all this instruction, they can be or not on both routines. it’s up to you.

Exit Sub
If I’m correct, it needs to go after the latest statement / routine, or inside of a conditional (IF, Case, Etc.). if you add it before error handler, it will exit earlier due the code it read 1 by 1 from the beginning to the end, executing consecutively.
 

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for the explanation.

With the exit sub, I was just referring to adding exit sub before errorhandler, otherwise the error handler would always run. Guess it doesn't matter in either example unless you have some code in errorhandler which is specific to an error.

Or does it actually need an exit sub in your example as the end sub will exit it

E.g you might have a message box in error handler section which you/user would only want to see if there was an error

So instead of the end of your code which is
VBA Code:
    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

ErrorHandler:
     Msgbox "Error in code!"
    Call Functions.OptimizeCodeSpeedRestore
 
    Exit Sub

End Sub

Have

VBA Code:
    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

Exit sub

ErrorHandler:
          Msgbox "Error in code!"
    Call Functions.OptimizeCodeSpeedRestore

End Sub
 

DrHacker

New Member
Joined
Jun 4, 2018
Messages
31
I add "Exit Sub" due sometimes the code do not end the routine properlly for some reason. So , i recomend you to add Exit sub to it.

In the other hand, if you want to show the Error Message, intead of a Message box only, i suggest you this code:

VBA Code:
If Err.Number <> 0 Then
 Msg = "Error # " & Str(Err.Number) & " was generated by " _
        & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
         MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If
 

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

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
Top