Hello Excellers and welcome to another blog post in my #macromonday 2019 series.   It is time for some easy Macro writing today.  Today’s Excel macro is really small but very useful.  Let’s write some simple a simple VBA Macro that will refresh all of the Pivot Tables in our worksheet at once.

Using The Manual Method To Refresh Pivot Tables

You can refresh all your Pivot Tables from the more manual or regular method follow the steps below.

• Data Tab | Connections Group | Refresh All (v 2016).

But that wouldn’t be as much fun on a Monday, would it?. I have found this quicker for users by attaching this small Macro to a Button on an Excel workbook, which allows novice Excel users to press a simple button themselves to refresh their Pivot Tables.

If you want more details on attaching a Macro to a button you can check out my blog post below.

Macro Mondays – How To Assign An Excel Macro To A Button or Assign A Button To An Excel Macro

Preparing To Write The Macro

First, you will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

As you can see this macro will be useful to reuse in any workbook with links that requires a Pivot Table refresh  Therefore I will create and save this macro for future use in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW)

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro

We need to start off the macro by inserting a New Module.  Do this by selecting the Personal.xlsb workbook, then Insert Module.  Type Sub then the name of your macro.  In this example, I have called it simply RefreshAllWorkbookPivotTables.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]

Sub RefreshAllWorkbookPivotTables()

End Sub

[/stextbox]

 

Declaring Variables

The next step in writing out macro if to declare a variable.  This ensures that Excel creates a memory container.  In this example, I  only have one variable that I need to declare -Dim PT As PivotTable

[stextbox id=’info’]

Dim PT As PivotTable

[/stextbox]

 

Loop Through All Pivot Tables

We use the For Each Next Loop to perform the same task multiple times.   It is used on a collection of objects such as sheets in a workbook, shapes on a sheet or as we are doing, Pivot Tables in a sheet.  You can’t run a ‘for each item’ loop on individual objects.  You need to have more than one object, so it will not work on one Pivot Table

[stextbox id=’info’]

For Each PT In ActiveSheet.PivotTables
PT.RefreshTable
Next PT

[/stextbox]

Ending The Macro

Once all of the Pivot Tables have been refreshed, the Macro ends.  This was already entered into the module for us when started the type the name of the macro.

[stextbox id=’info’]

End Sub

[/stextbox]

 

All you need to do is test your new macro.

Want The Code? You can copy it right here

[stextbox id=’info’]

Sub RefreshAllWorkbookPivotTables()

Dim PT As PivotTable

For Each PT In ActiveSheet.PivotTables
PT.RefreshTable
Next PT

End Sub

[/stextbox]

 

 

If you want more Excel and VBA Macro tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

How To Excel At Excel – Macro Mondays Blog Posts.

 

Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.