Time for some easy Macro writing today. Let’s write some simple VBA code that will refresh all of the Pivot Tables in our Excel workbook at once. You can refresh all your Pivot Tables from the Analyse Tab | Data Group. 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.
What Does The Macro Do?
Refreshes all Pivot Tables in your Excel workbook at once using the VBA For Each … In … Next Loop method to loop through the Pivot Tables in the workbook.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module
Step 1. We need to declare a variable PT as Pivot Table. This will create a memory container in Excel for each Pivot Table the macro loops through.
Step 2. We use the VBA For Each … In … Next Loop method to loop through the Pivot Tables one at a time starting with the first in the collection. There must be more than one item in the collection of objects, or you will get an error when you try to run the Macro.
Step 3. Once all of the Pivot Tables have been refreshed, the Macro ends.
Step 4. Test Your Macro!
Want The Code? You can copy it right here
If you want more Excel and VBA 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 or the example worksheets you can do so by clicking on the links below.
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.