How to Automatically Record a Daily Excel Value Without Manual Copying
Question details
The user needs to automatically save a daily snapshot of a financial total in Excel so that historical records remain static when the source data updates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking daily financial totals or dynamic metrics over time without losing historical data.
- Observed behavior
- Formulas update dynamically, which overwrites past totals, requiring a solution to permanently preserve the value at a specific time each day.
Determine whether you are using the desktop version of Excel or Excel for the web, as this will dictate whether you should use VBA or Power Automate for your scheduled tasks.
Use a VBA Macro to Record Daily Values in Desktop Excel
Ideal for desktop users, a VBA macro can automatically copy the dynamic formula result and paste it as a static value into a historical log table.
Since formulas update automatically, you need a script to convert the formula result into a static value. VBA (Visual Basic for Applications) can automate this copy-paste process and even add timestamps to your records.
Press Alt + F11 to open the VBA Editor, then click Insert > Module to create a new script.
Create a macro that targets your source cell (e.g., Range("B2").Copy) and uses PasteSpecial Paste:=xlPasteValues to paste it into the next empty row of your tracking sheet.
In the same macro, instruct VBA to write the current date into the adjacent column using the Date function so you know exactly when the value was recorded.
Call this macro from the Workbook_Open event or use Windows Task Scheduler to open the file daily, ensuring the snapshot is captured automatically.
Use Power Automate for Excel on the Web
Best for cloud-stored workbooks, Power Automate can run scheduled flows to read and log data without keeping the Excel file open on your local computer.
Manage Your Spreadsheets and Macros with WPS Office
Need to automate daily data logs but looking for a more cost-effective solution? WPS Office provides a lightweight, highly compatible spreadsheet editor that seamlessly opens and edits Microsoft Excel files, including those containing complex macros.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm file without losing any formatting or data.
- 3. Run Macros and Automate: Enable macros if prompted, and continue automating your daily data tracking smoothly.
Frequently Asked Questions
Can I use a standard Excel formula to save historical data?
No. Standard Excel formulas recalculate dynamically whenever the source data changes. To permanently preserve a value, you must use a method that converts the formula result into a static value, such as copy-pasting as values, VBA, or an external automation tool like Power Automate.
How do I trigger a VBA macro automatically every day?
You can use the Application.OnTime method within VBA to schedule the script while the workbook remains open. Alternatively, you can use Windows Task Scheduler to automatically open the Excel file at a specific time and use the Workbook_Open event to run the macro upon launch.
Will Power Automate record values if my computer is turned off?
Yes. Power Automate runs entirely in the cloud. As long as your Excel file is stored on a cloud service like OneDrive or SharePoint, the scheduled flow will execute and append the data to your table even if your local computer is powered off.
