As we all know, whenever we make changes in a source data of a pivot table, it doesn't reflect immediately in the pivot table. We need to refresh the pivot tables to see the changes. And if you send an updated file without refreshing the pivot tables, you may feel embarrassment.
So in this article, we will learn how to auto refresh a pivot table using VBA. This way is easier than you imagined.
This is the simple syntax to automatically refresh pivot tables in the workbook.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
Every pivot table stores the data in pivot cache. This is why pivot is able to show previous data. When we refresh pivot tables, it updates the cache with new source data to reflect the changes on the pivot table.
So we just need a macro to refresh the cache of pivot tables. We will do this using a worksheet event so that we don't have to run macro manually.
If your source data and pivot tables are in different sheets, then the VBA code should go in the source data sheet.
Here we will use Worksheet_SelectionChange Event. This will make the code run whenever we switch from the source data sheet to another sheet. I'll explain later why I used this event.
Here, I have source data in sheet2 and pivot tables in sheet1.
Open VBE using CTRL+F11 key. In project explorer, you can see three objects, Sheet1, Sheet2 and the Workbook.
Since Sheet2 contains the source data, double click on sheet2 object.
Now you can see two drop downs at the top of the code area. From the first drop down, select the worksheet. And from the second drop down select Deactivate. This will insert an empty sub name Worksheet_Deactivate. Our code will be written in this sub. Any lines written in this sub, get's executed as soon as the user switches from this sheet to any other sheet.
On sheet1 I have two pivot tables. I want to refresh only one pivot table. For that, I need to know the name of the pivot table. To know the name of any pivot table, select any cell in that pivot table go to pivot table analyze tab. On the left hand side, you will see the name of the pivot table. You can also change the name of the pivot table here.
Now we know the name of the pivot table, we can write a simple line to refresh the pivot table.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
And it is done.
Now whenever you will switch from the source data, this vba code will run to refresh the pivot table1. As you can see in the gif below.
In the above example, we only wanted to refresh one specific pivot table. But if you want to refresh all the pivot tables in a workbook, you just need to do slight changes to your code.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
In this code we are using a For loop to loop through each pivot caches in the workbook. The ThisWorkbook object contains all the pivot caches. To access them we use ThisWorkbook.PivotCaches.
Why Use Worksheet_Deactivate Event?
If you want to refresh the pivot table as soon as any change is made in source data, you should use Worksheet_Change event. But I don't recommend it. It will make your workbook run the code every time you make any change in the sheet. You may have to do hundreds of changes before you want to see the result. But excel will refresh the pivot table on every change. This will lead to waste of processing time and resources. So if you have pivot tables and data in different sheets, it is better to use Worksheet Deactivate Event. It lets you finnish your work. Once you switch to pivot table sheets to see the changes, it amends the changes.
If you have pivot tables and source data on the same sheet and you want pivot tables to auto refresh it self, you may want to use Worksheet_Change Event.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
How to Refresh Everything in Workbooks When A Change is Made in Source Data?
If you want to refresh everything on a workbook (charts, pivot tables, formulas, etc) you can use ThisWorkbook.RefreshAll command.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Please note that this code does not change the data source. So if you add data below the source data, this code won't include that data automatically. You can use Excel Tables to store source data. If you don't want to use tables, we can use VBA for including new data too. We will learn it in the next tutorial.
So yeah mate, this is how you can automatically refresh the pivot tables in Excel. I hope I was explanatory enough and this article served you well. If you have any questions regarding this topic, you can ask me in the comments section below.
Related Articles:
How to Dynamically Update Pivot Table Data Source Range in Excel: To dynamically change the source data range of pivot tables, we use pivot caches. These few lines can dynamically update any pivot table by changing the source data range. In VBA use pivot tables objects as shown below...
Run Macro If Any Change Made on Sheet in Specified Range: In your VBA practices, you would get the need to run macros when a certain range or cell changes. In that case, to run macros when a change is made to a target range, we use the change event.
Run Macro When any Change is Made On Sheet | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.
Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.
The Worksheet Events in Excel VBA | The worksheet event are really useful when you want your macros to run when a specified event occurs on the sheet.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
