Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we may have to refresh certain values for generating updated output after a fixed interval. There are a few methods available to auto-update excel worksheets. But you’ve to apply VBA if the interval period is in seconds. This article will show you 4 ideal examples of Auto Update with Interval 5 Seconds in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
4 Ideal Examples of Auto Update with Interval 5 Seconds in Excel
To auto-refresh certain data values after every specified second, we’ve to take the help of Excel VBA. No other way is available when the interval period is in seconds. A couple of other ways are available for the minutes. In this article, we’ll only consider the interval in second cases. To illustrate, we’ll use the following dataset as an example. For instance, we have Value 1 & Value 2 which we generate using the RAND function.
In cell C6, we apply the NOW function to get the Time.
Lastly, in cell C7, we input the TODAY function for the Date.
1. Auto Update Cell Every 5 Seconds with Excel VBA
In our first example, you’ll see how to auto-update a single cell every 5 seconds. Therefore, follow the steps below to perform the task.
- First, go to the Development tab.
- Then, select Visual Basic.
- As a result, the VBA window will pop out.
- There, select the Insert tab.
- After that, click Module from the drop-down.
- Consequently, the Module window will appear.
- Copy the following code and paste it into the box.
Sub UpdateCell() Worksheets(1).Calculate Range("C4").Calculate Application.OnTime DateAdd("s", 5, Now), "UpdateCell" End Sub
- Now, save the file.
- Next, press the F5 key to run the code.
- Thus, it’ll refresh cell C4 every 5 seconds.
2. Apply VBA Code to Refresh Cell Range with Interval 5 Seconds
Moreover, you may want to refresh a cell range instead of just a cell. Here, you’ll learn the VBA code to carry out that operation. So, learn the following process.
- Firstly, go to Development ➤ Visual Basic.
- Afterward, click Insert ➤ Module.
- The Module window will emerge.
- Copy the below code and paste it there.
Sub UpdateCellRange() Worksheets(2).Range("C4:C7").Calculate Application.OnTime DateAdd("s", 5, Now), "UpdateCellRange" End Sub
- Next, save the code and press F5.
- As a result, it’ll run the code.
- In this way, the cell range C4:C7 will get updated every 5 seconds.
- Refresh All Pivot Tables in Excel (3 Ways)
- How to Auto Refresh Pivot Table in Excel (2 Methods)
- VBA to Refresh Pivot Table in Excel (5 Examples)
- Pivot Table Not Refreshing (5 Issues & Solutions)
- How to Refresh All Pivot Tables with VBA (4 Ways)
3. Automatically Update Excel Worksheet Using VBA
However, we can also auto-update the entire worksheet. Here. we’ll update the 3rd worksheet. Hence, learn the following steps.
- First of all, repeat the steps in example 1 or 2 to get the Module window.
- Now, copy the code and input it there.
Sub UpdateSheet() Worksheets(3).Calculate Application.OnTime DateAdd("s", 5, Now), "UpdateSheet" End Sub
- After saving the file, press F5.
- Accordingly, the 3rd worksheet will get refreshed every 5 seconds.
4. Auto Refresh Excel Workbook with Interval 5 Seconds
Finally, if you want to refresh the entire workbook, go through the process below and learn the code.
- In the beginning, get the Module dialog box by repeating the steps in example 1.
- Place the following code in the Module box.
Sub UpdateWorkbook() Worksheets(1).Calculate Worksheets(2).Calculate Worksheets(3).Calculate Worksheets(4).Calculate Application.OnTime DateAdd("s", 5, Now), "UpdateWorkbook" End Sub
- This code will refresh all 4 worksheets you have in the workbook.
Henceforth, you will be able to Auto Update with Interval 5 Seconds in Excel following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
- How to Refresh Chart in Excel (2 Effective Ways)
- Disable Background Refresh in Excel (2 Handy Methods)
- Excel VBA: Turn Off Screen Update
- [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
- How to Refresh Pivot Table in Excel (4 Effective Ways)
- How to Update a Pivot Table Automatically When Source Data Changes