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

Method 1 – Using the ThisWorkbook Method in VBA to Refresh an Excel Sheet Automatically

Step 1 – Creating the Dataset

We will create a Stock dataset as Stock data needs to be refreshed from time to time.
Type the Company Name in cells B5:B10.

These are the companies in the National Stock Exchange India.

Select cells B5:B10.
Go to the Data tab.
Select Stocks.

The Stocks 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.

You will see the full form of the Company Name.
Click on the symbol on the left side of the company name in cell B5.

Select Price, and click on the button to add the column next to the listing.

You can see the Price in cell C5.

Put the Prices for other companies as well.
You can see the complete dataset.

Step 2 – Applying VBA

Go to the Developer tab and select Visual Basic.

A VBA editor window will appear.
Double-click on ThisWorkbook.

Click on the drop-down arrow of the General box.

Select Workbook.
Click on the drop-down arrow of the Declarations box and select Open.

Copy the following code into the window:

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

Code Breakdown

The Private Sub runs wherever a change is made to the workbook.
We used the ThisWorkbook method to refresh the entire workbook.
We used a MsgBox to show “Refreshed”.

Save the code and close the VBA editor window.
Return to the Workbook.
Close the Workbook.
Reopen the Workbook.
As soon as you open the Workbook, the data will get refreshed. For some data types, you might need to wait a few days to see a noticeable change.
You can see a MsgBox showing Refreshed.
Click OK in the MsgBox.

Method 2 – Applying the Calculate Method to Refresh an Excel Sheet Automatically

Step 1 – Making the Dataset

In cells B5:B10, we input the Order No.
Copy the following formula in cell C5:

=RANDBETWEEN(12,34)

The RANDBETWEEN function yields a random integer between two Bottom and Top numbers that we specify.

Press Enter.
Drag down the formula with the Fill Handle tool.

You can see the complete dataset.

Step 2 – Using VBA

Go to the Developer tab and select Visual Basic.

A VBA editor window will appear.
Double-click on Sheet4 (Applying Calculate Method).

Click on the drop-down arrow of the General box.
Select Workbook.

Click on the drop-down arrow of the Declarations box and select SelectionChange.

Copy 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

Code Breakdown

The Private Sub runs wherever a change is made on the worksheet.
We use the Ontime function to refresh the worksheet every 4 minutes.
We created a Sub for the Ontime function so that the calculation gets refreshed.

Save the code and close the VBA editor window.
Go back to the sheet.
Click on a cell in the range C5:C10.
As soon as you click on a cell, the sheet gets refreshed.
You will get a MsgBox showing Changed.
Click OK.

Method 3 – Refreshing a 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 and use code to make it refresh automatically.

Step 1 – Inserting Pivot Table

Select the entire dataset by selecting cells B4:D9.
From the Insert tab, select PivotTable.
Select From Table/Range.

A PivotTable from table or range dialog box will appear.
Select Existing Worksheet.
Select cell B12 in the Location box.
Click OK.

A PivotTable Fields dialog box will appear at the right end of the worksheet.
Mark Product and drag it to the Rows group.
Mark Sales and Profit and drag them to the Values group.

You will see the Pivot Table.

Step 2 – Inserting VBA

Go to the Developer tab and select Visual Basic.

A VBA editor window will appear.
Double-click on Sheet5 (Pivot Table 1).

Click on the drop-down arrow of the General box.
Select Workbook.
Click on the drop-down arrow of the Declarations box.

Select Change.

Copy the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

   ThisWorkbook.RefreshAll

End Sub

Code Breakdown

The Private Sub runs wherever a change is made on the Pivot Table.
We used the ThisWorkbook method to refresh the entire Pivot Table.

Save the code and Close the VBA editor window.
Return to the workbook.

The Profit for Battery in cell D9 is $3,300. Along with that, in the Pivot Table, the Profit for Battery in cell D13 is $3,300.

Edit the value in cell D9 to $4,000.
As soon as you press Enter the Pivot Table gets refreshed.
You can see $4,000 in cell D13 of the Pivot Table.

Method 4 – Refreshing All Pivot Tables Using VBA

Steps:

Create a Pivot Table in Pivot Table 2 sheet.

Create another Pivot Table in Pivot Table 3 sheet.
Go to the Developer tab and select Visual Basic.

From the Insert tab, select Module.

Copy 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

Code Breakdown

We declare refresh_all_PivotTable_in_Excel_sheet as the Sub.
We take pivot_cache as PivotCache.
We use the For Next Loop to run the code until it finds the last Pivot Table.