Excel Functions Tutorials

• Refresh Pivot Table Without Macro
• Ms Excel Refresh Pivot Table Macro

• VBA

Refresh Pivot Table Without Macro

Aug 17, 2017  If you have one or two pivot tables it’s ok to refresh them manually. But if you more than that it’s good to go with Pivot Table Refresh macro. Trust me with the help of this macro you can save a lot of time. Start using this macro from today and let me know if you have any issues or suggestions. You can refresh the data for PivotTables connected to external data, such as a database (SQL Server, Oracle, Access, or other), Analysis Services cube, data feed, and many other sources. You can also refresh data from a source table in the same or a different workbook.

Related Courses

Refresh Pivot Table in VBA

The pivot table is vital in analyzing the huge amount of data. It helps right from analyzing, summarizing, as well as making useful data interpretation. However one of the problems with this pivot table is it will not automatically get refreshed if there is any change in the source data, the user has to refresh the pivot table in VBA by going to the particular pivot table every time when there is a change.

But say goodbye to the manual process because here we have the VBA method to refresh the pivot table as soon as you make any change in the pivot table.

Method #1 – Auto Refresh Pivot Tables

The only time pivot table needs to be updated is whenever there is any change in the source data of the pivot table we are referring to.

For example, look at the below data and pivot table.

Now I will change the numbers in source data i.e. from A1 to B17.

In cell B9 I have to change the value from 499 to 1499 i.e. 1000 increase in the data but if you look at the pivot still shows the result as 4295 instead of 5295. I have to manually refresh my pivot table to update the pivot table.

To overcome this issue we need to write a simple macro code to refresh the pivot table in VBA whenever there is any change in the source data.

Method #2 – Write a Simple Macro to Refresh All

We need to trigger the change event of the datasheet. In the visual basic editor double click on the datasheet.

Ms Excel Refresh Pivot Table Macro

Once you double click on the sheet select “Worksheet” and select the event as “Change”.

You will see an auto sub procedure opened as Worksheet_Change(ByVal Target As Range)

Refer to the datasheet by using the Worksheets object.

Refer to the pivot table name by the name of the pivot table.

Select the method as “Refresh Table”.

Now, this VBA code will refresh the pivot table “PivotTable1” whenever there is any change in the source data sheet. You can use the below code to refresh the pivot tables in VBA, you just have to change the pivot table name.

Code:

Method #3 – Refresh All the Pivot Tables in the Same Worksheet

If you have many pivot tables in the same worksheet you can refresh all the pivot tables in single click itself. Use the below code to refresh all the pivot tables in the sheet.

Code:

You need to change the name of the worksheet and pivot table names as per your worksheet details.

Method #4 – Refresh All the Pivot Tables in the Workbook

It is highly unlikely we have all the pivot tables on the same worksheet. Usually, for each report, we try to add separate pivot tables in separate sheets. In these cases, we cannot keep writing the code each pivot table to be refreshed.

So, what we can do is with a single code using loops we can loop through all the pivot tables on the workbook and refresh them with a single click of the button.

Below code will loop through each pivot table and refresh them.

Code 1:

Code 2:

Both the codes will do the refreshing of pivot tables.

If you want the pivot table to be refreshed as soon as there is any change in the datasheet of the pivoting sheet you need to copy and paste the above codes to the Worksheet Change event in that workbook.

Method #5 – Avoid Loading Time by Using Worksheet Deactivate Event

When we use the “Worksheet Change” event it keeps refreshing even when there is no change in the data source but if any change happens in the worksheet.

Even if you enter one single dot in the worksheet it tries to refresh the pivot table in VBA. So to avoid this we can use the “Worksheet Deactivate” method instead of the “Worksheet Change” method.

Deactivate the event updates on the pivot table when moving from one sheet to another sheet.

Recommended Articles

This has been a guide to VBA Refresh Pivot Table. Here we learn auto-refresh pivot tables using VBA Macros Code along with examples and a downloadable template. Below are some useful excel articles related to VBA –

• 3 Courses
• 12 Hands-on Projects
• 43+ Hours
• Full Lifetime Access
• Certificate of Completion