# Refresh All Data Connections and Pivot Tables with Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

### 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.
=D5+D5*(5%)

• 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.
=E5+E5*(2%)

• After that, press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.

### 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.

### 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.

### 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.

### 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.

## Related Articles

#### Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF