How to automatically refresh a Pivot Table in Excel?
Normally, when we update the source data of a pivot table, the pivot table won’t be updated until we click Analyze > Refresh manually. This will be quite boring if the pivot table stays in another worksheet and you may forget to refresh it. Here, this article will introduce a VBA to automatically refresh a pivot table in Excel.
Automatically refresh a Pivot Table by VBA
Automatically refresh a Pivot Table by VBA
Please follow below steps to apply the VBA for automatically refreshing the specified pivot table in Excel.
1. In the Sheet tab bar, right-click the sheet tab that contains the source data, and select View Code in the context menu. See screenshot:
2. In the opening Microsoft Visual Basic for Applications window, please paste code.
VBA: Auto refresh the specified pivot table
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("sheet name").PivotTables("PivotTable name").PivotCache.Refresh
End SubNotes:
(1) In the code of Worksheets("sheet name").PivotTables("PivotTable name").PivotCache.Refresh, please replace sheet name with the name of the sheet containing the specified pivot table, and replace the PivotTable name with the name of the specified pivot table.
(2) Select any cell in the specified pivot table, you will get its name on the Analyze tab (or Options tab). See screenshot:
3. Save the code.
From now on, when you change the data in the source data, the specified pivot table will be refreshed automatically.
Easily combine multiple worksheets/workbooks/CSV files into one worksheet/workbook
It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks!
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
