Welcome back to another #MacroMonday #Excel tip in my 2019 series. This is an answer to a question from a subscriber of my YouTube Channel. They wanted to know how to get their Pivot Table or Pivot Tables to automatically refresh when the source data was changed. Whilst there is no default setting in Excel to refresh a Pivot Table when the source data is changed automatically, we can write a small bit of Excel VBA macro code to do this for us.

Non-VBA Ways To Refresh A Pivot Table.

Before we go ahead and write some simple code. Let’s look at the non-VBA ways to refresh a Pivot Table. There are a few, so I will run through them one by one.

Manually Refresh A Pivot Table.

This probably the method that most Excel users know to refresh their Pivot Tables.

• Click and place the cursor anywhere in the Pivot Table, this enables the Pivot Table Options menu.
• Hit Analyze | Refresh or hit the Excel shortcut Alt+F5.
• To refresh all Table select Refresh All.

Alternatively, you can follow the steps below.

• Right Click in the Data area of the Pivot Table
• Select Refresh.

 

Semi-Automated Pivot Table Refresh.

I have called this section a semi-automated version of the Pivot Table refresh. This is because it does automatically refresh at times we can specify but NOT automatically when our source data is updated.

Refresh When The Workbook Opens.

We can set the Pivot Tables to fresh every time the workbook is opened. This forces a refresh.

• Right-Click on any Pivot Table in you Exel workbook.
• Select Pivot Table Options | Data Tab
• Check the Refresh data when opening the file option.
• Click Ok to confirm the change.

Refresh At Intervals.

We can also set the Pivot Tables to refresh at specified intervals to ensure that users are getting the correct information. In order for this method to be available, you will need to save the Pivot Table to the Excel Data Model.

• Click anywhere in your data set
• Click the Insert tab, and then click the Pivot Table in the Tables group.
• Check the Add this data to the Data Model option.
• Change any other settings you need to.
• Click OK.

Now you have added your Pivot Table to the Excel Data Model, you can make the refresh settings appropriate to your requirements.

• Click anywhere inside the Pivot.
• Click the contextual Analyze tab.
• Choose Connection Properties from the Change Data Source drop.
• In the Refresh Control Area select the interval you want your data to be refreshed.
• Hit Ok.

So, let’ now move into setting up some simple Excel VBA code to actually remove any of these manual or semi-automatic processes. We are going to write THE simplest bit of code that updates the Pivot Table as soon as the source data is changed.

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. This macro relates to the Excel worksheets that the data is stored in. So, instead of inserting a module to store the code we need to insert the code in the worksheet that contains our source data. Once you have located the worksheet in the Project Explorer window as in the screenshot below. Double click on it to open up the Code Editor.

Note: if the Code Editor is not visible then use the Excel Shortcut Ctrl+R to enable it.

 

Add A New Event For Worksheet Change.

We already know that this macro is based upon the source data changing. Therefore the trigger for this macro to work is a change in the Excel worksheet. The next steps reflect this. We need to add a new event for Worksheet Change.

Double-clicking on the sheet opens up the code module for that object. Within the code module, we want to create an event macro. To do so, choose Worksheet in the Object drop-down box on the left. Next in the right-hand drop by box select change. Excel has already inserted the Worksheet_SelectionChange event to the module. We don’t need this, so go ahead and delete it.

Writing The VBA Code

The Worksheet_Change event macro will run any time a change is made to cells in our worksheet. We now need to can add VBA code to the Worksheet_Change event to perform actions when the user edits cells. So now it is time for the code.

[stextbox id=’info’]

ThisWorkbook.RefreshAll

[/stextbox]

This RefreshAll code will actually refresh any Pivot Tables, Data Connections or queries in the Worksheet. Yes, that is it. It is all of the code that you need. How about testing it out.? Just change the source data and your Pivot Table will update.

Testing The VBA Code.

Now, there are a few ways to test if the code is working. But, I find the simplest of all is to use the WATCH WINDOW. Just follow the steps below.

• Formula Tab | Formula Auditing Group | Watch Window.
• Select the worksheet cell you want to watch ( and see update). In this example, it is Sheet2 and cell B7. This is the Grand Total cell of my Pivot Table. Any changes to to the data source I will see updated in this cell. Sounds a good place to check to me.

So, I will change one of the values in my data source worksheet. The Grand Total should update. Let’s give it a go. The current Total is 203.39.

• Click on your data source worksheet. The Watch Window will remain on your screen.
• Change one of the data cells.
• The cell should change as the macro works.

You can see the value updating in the Watch cell. It is working. Great. I can automatically refresh Excel now.

 

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 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 Monday series. Click on the link below

Macro Mondays Blog Posts.

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.