Bishawajit Chakraborty
After you create a pivot table, you must use the change data source command to update or refresh the data in it. However, it is time-consuming and inconvenient to manually update the pivot table. You must therefore learn how to build an automated pivot table that will constantly refresh its data. As a result, we’ll demonstrate in this article how to automatically refresh all data connections and pivot tables with Excel VBA.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Step-by-Step Procedures to Refresh All Data Connections and Pivot Tables with Excel VBA
This article will demonstrate how to create multiple pivot tables and refresh all data connections and pivot tables using VBA in Excel.
Step 1: Making Data Set
Here, we will create our sample dataset, including the sales, profit, and funds of different sellers.
- In order to calculate tracking errors, we will create a dataset.
Read More: How to Refresh Data Connection in Excel Without Opening File
Step 2: Using Formula to Connect All Data
Here, we will determine all the profits and funds for all the sellers.
- Firstly, select cell E5.
- Secondly, write down the following formula.
- Then, press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.
- Firstly, choose cell F5.
- Then, write down the following formula.
- After that, press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.
Read More: Excel Connections vs Queries: Main Dissimilarity with Example
Step 3: Creating Pivot Tables from Source Data Set
Here, we will show how to create multiple pivot tables from a data set using the PivotTable command.
- Firstly, click on the Insert tab to insert a pivot table.
- Then, click on the PivotTable command from the PivotTables
- After that, select the From Table/Range option from the list.
- Therefore, select the table range with the header.
- Besides, choose the New Worksheet option to place the pivot table on a new worksheet.
- Then, click OK.
- Finally, you will see the pivot table for the sum of sales and profits for all the sellers.
- And, you will see another pivot table for the sum of sales and funds for all the sellers.
- But, we want to refresh these two pivot tables’ data by changing the main data set with Excel VBA.
Read More: How to Create a Data Source in Excel (with Simple Steps)
Step 4: Altering Value in Source Data Set
Now, we will demonstrate the main data set with some changes and a connected cell data value to show the final changes of all the pivot tables’ grand total.
- So, we will put 25000 in cell D15, and then we can see the changes in the corresponding cell value of E15 and F15.
Read More: [Fixed]! Data Connection Not Refreshing in Excel
Step 5: Applying VBA Code to Refresh All Data Connections and Pivot Tables
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to refresh all data connections and pivot tables in Excel.
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic command.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write the VBA code.
- Now, paste the following VBA code into the Module.
- Then, save the following code.
Sub Refresh_All_Data_Connections_and_Pivot_Tables()
Dim Table As PivotTable
For Each Table In ActiveSheet.PivotTables
Table.RefreshTable
Next Table
End Sub- Therefore, press F5 to run the program.
Read More: How to Create Excel Data Connection to Another Excel File
Step 6: Showing Final Output with Refreshed values in Pivot Tables
In this section, we will show you all the refreshed data in the pivot tables.
- Finally, you will get all the refreshed values of these two pivot tables.
- Therefore, you will see here the changes in the grand total of sales and profits after refreshing.
- Therefore, this given image will show here the changes in grand total of sales and funds after refreshing.
Conclusion
In this article, we’ve covered step-by-step procedures to refresh all data connections and pivot tables with Excel VBA. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
