How to Refresh Excel Sheet Automatically Using VBA (4 Methods)

If you want to refresh Excel sheet automatically VBA, you have come to the right place. Here, we will walk you through 4 easy methods to do the task smoothly.

How to Refresh Excel Sheet Automatically Using VBA: 4 Possible Methods

In the following article, we will describe 4 easy and effective methods to refresh Excel sheet automatically VBA.  Here, we used Microsoft Excel 365. You can use any available Excel version.

1. Using ThisWorkbook Method in VBA to Refresh Excel Sheet Automatically

In this method, we will use the ThisWorkbook method in VBA to refresh Excel sheet automatically.

Let’s go through the following steps to do the task.

Step-1: Creating Dataset

To refresh Excel sheet automatically VBA, first, we have to create a dataset. Here, we will create a Stock dataset as Stock data needs to be refreshed from time to time.

• First, we will type the Company Name in cells B5:B10.

These are the companies enlisted in the National Stock Exchange India.

• After that, we select cell B5:B10 >> go to the Data tab.
• Further, we will select Stock.

Here, the Stock option in the Data tab is only available in Excel 365. Therefore, if you do not have Excel 365, you can work with other available data.

As a result, you can see the full form of the Company Name.

• Further, we will click on the symbol that is on the left side of the company name of cell B5, marked with a red color box.

• After that, we will select Price, and click on the button right side of the price which is marked with a red color box.

Hence, you can see the Price in cell C5.

• In a similar way, we find out Prices for other companies as well

Therefore, you can see the complete dataset.

Step-2: Applying VBA

In this step, we will apply VBA and we will use the ThisWorkbook method.

• First of all, we will go to the Developer tab >> select Visual Basic.

At this point, a VBA editor window will appear.

• Furthermore, we will double-click on ThisWorkbook.

After that, we will click on the drop-down arrow of the General box.

• Then, we will select Workbook.
• Furthermore, we will click on the drop-down arrow of the Declarations box >> select Open.

• After that, we will type the following code.

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    MsgBox "Refreshed"
End Sub

• Afterward, we will Save the code >> Close the VBA editor window.
• Then, we will return to our Workbook >> close our Workbook.
• Furthermore, we will open our Workbook.

As soon as we open our Workbook, our data will get refreshed.

Along with that, you can see a MsgBox showing Refreshed.

• Then, click OK in the MsgBox.

Therefore, we can refresh Excel sheet automatically using VBA.

Read More: How to Refresh Excel Sheet Automatically

2. Applying Calculate Method to Refresh Excel Sheet Automatically

In this method, we will use the Calculate method to refresh Excel sheet automatically VBA.

Let’s go through the following steps to do the task.

Step-1: Making Dataset

In this step, we will make a dataset using which we will show how to refresh the Excel sheet automatically using VBA.

Here, in cells B5:B10, you can see Order No.

Next, we will calculate Random Number using the RANDBETWEEN function.

• To do so, we will type the following formula in cell C5.

The RANDBETWEEN function yields an integer number between two Bottom and Top numbers that we specify.

• After that, press ENTER.

As a result, you can see the result in cell C5.

• Moreover, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete dataset.

Step-2: Using VBA

In this step, we will use VBA with the Calculate method to refresh the Excel sheet automatically.

• First of all, we will go to the Developer tab >> select Visual Basic.

After that, a VBA editor window will appear.

• At this point, we will double-click on Sheet4 (Applying Calculate Method).

• Afterward, we will click on the drop-down arrow of the General box.
• Then, we will select Workbook.

• Furthermore, we will click on the drop-down arrow of the Declarations box >> select SelectionChange.

• Then, we will type the following code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.OnTime Now + TimeValue("00:4:00"), "Refresh_try"
    MsgBox " Changed"
End Sub

Sub Refresh_try()

Range("C5:C10").Calculate
End Sub

•  After that, we will Save the code and Close the VBA editor window.
• Then, we will go back to our Sheet4 (Applying Calculate Method).
• Furthermore, we will click on a cell in the sheet.

As soon as we click on a cell, the sheet gets refreshed.

Along with that, we can see a MsgBox showing Changed.

• Then, click OK.

Read More: How to Auto Update with Interval 5 Seconds in Excel

3. Refreshing Pivot Table Using VBA

Here, you can see the following dataset has the Product, Sales, and Profit columns. Using this dataset, we will insert a Pivot Table.

After that, we will use VBA to refresh Pivot Table automatically.

Let’s go through the following steps to do the task.

Step-1: Inserting Pivot Table

In this step, we will insert a Pivot Table.

• First, we will select the entire dataset by selecting cells B4:D9.
• After that, from the Insert tab >> we will select PivotTable.
• Then, we select From Table/Range.

Moreover, a PivotTable from table or range dialog box will appear.

• Then, we will select the Existing Worksheet.
• Furthermore, we will select cell B12 in the Location box.
• After that, click OK.

Afterward, a PivotTable Fields dialog box will appear at the right end of the worksheet.

• At this point, we will mark Product >> drag it to the Rows group.
• Then, we will mark Sales and Profit >> drag them to the Values group.

As a result, you can see the Pivot Table.

Step-2: Inserting VBA

In this step, we will insert VBA to refresh PivotTable automatically.

• In the beginning, we will go to the Developer tab >> select Visual Basic.

Next, a VBA editor window will appear.

• At this point, we will double-click on Sheet5 (Pivot Table 1).

• Afterward, we will click on the drop-down arrow of the General box.
• Then, we will select Workbook.
• Along with that, we will click on the drop-down arrow of the Declarations box.

• Afterward, we will select Change.

• Next, we will type the following Code.

Private Sub Worksheet_Change(ByVal Target As Range)

   ThisWorkbook.RefreshAll

End Sub

• Then, we Save the code >> Close the VBA editor window.
• After that, we return to our workbook.

Here, you can easily notice that the Profit for Battery in cell D9 is $3300.

Along with that, in the Pivot Table, the Profit for Battery in cell D13 is $3300.

• After that, we edited the value in cell D9 to $4000.
• Here, as soon as we press ENTER after editing the value of cell D9, the Pivot Table gets refreshed.

Therefore, you can see $4000 in cell D13 of the Pivot Table.

4. Refreshing All Pivot Tables Using VBA

In this method, we will use VBA to refresh all Pivot Tables in Excel Sheets.

Steps:

• First, we followed Step-1 of Method-3 to create the following Pivot Table in Pivot Table 2 sheet.

• Along with that, we followed Step-1 of Method-3 to create the following Pivot Table in Pivot Table 3 sheet.

Next, we will use VBA so that we can refresh multiple Pivot Tables.

• Afterward, we will go to the Developer tab >> select Visual Basic.

At this point, a VBA editor window will appear.

• After that, from the Insert tab >> select Module.

• Next, we will type the following code in the Module.

Sub refresh_all_PivotTable_in_Excel_sheet()

Dim pivot_cache As PivotCache

  For Each pivot_cache In ThisWorkbook.PivotCaches
    pivot_cache.Refresh
  Next pivot_cache

End Sub

• Then, we Save the code >> Close the VBA editor window.
• Afterward, we return to our Pivot Table 2 sheet.
• Here, we edit the Profit for Egg in cell D9 as $4000.

However, you can easily notice that in the Pivot Table, the Profit for Egg in cell D13 is $3300.

Next, we will run the code to refresh the Pivot Table.

• Furthermore, we will go to the Developer tab >> select Macros.

• After that, we will select refresh_all_PivotTable_in_Excel_sheet from the Macro Name.
• In addition, click Run.

As a result, you can notice that cell D13 now has a value of  $4000.

Hence, we can refresh Pivot Table on any of the sheets.

Practice Section

You can download the above Excel file to practice the explained methods.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Conclusion

Here, we tried to show you 4 methods to refresh Excel sheet automatically VBA. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.