How to Automatically Record a Daily Cell Value in Excel using VBA
Question details
The user wants to create a daily history log of a changing share value by saving a specific cell's value under the matching date without it recalculating.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking daily dynamic data (such as share values or inventory) where past data points need to be stored permanently in a historical timeline.
- Observed behavior
- Standard formulas dynamically recalculate and overwrite historical values. A VBA script is required to capture the current value and paste it as static text into the permanent record.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the VBA script can execute.
Use a VBA Macro to Log Daily Values
Implement a VBA macro to automatically find today's date in your timeline and paste the target cell's value as a static number, preserving historical data.
Formulas alone cannot create a persistent history because they continually update to display current values rather than storing past values. A macro bridges this gap by copying the value and pasting it as static data.
Press the ALT + F11 keys on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.
In the VBA Editor, click on 'Insert' in the top menu and select 'Module'. This will create a blank window where you can write your script.
Paste your custom script to find today's date and log the value. For example, create a 'Sub LogShareValue()' that sets the target worksheet, finds the current 'Date', locates the matching date column in your header row, and pastes the value from your source cell (like B2) into the corresponding blank row.
To make the macro run automatically every day, double-click 'ThisWorkbook' in the left Project pane. Select 'Workbook' from the top-left dropdown and 'Open' from the right dropdown. Inside 'Private Sub Workbook_Open()', type 'Call LogShareValue' so the logging triggers immediately upon opening the file.
Click 'File' > 'Save As'. In the file format dropdown, select 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your VBA code is saved permanently.
Record Daily Values Using Macros in WPS Spreadsheet
WPS Spreadsheet provides robust support for VBA and macros, allowing you to easily automate repetitive data entry tasks and maintain daily historical logs seamlessly.
- 1. Open your macro workbook: Launch WPS Office and open your .xlsm data tracking workbook.
- 2. Access the Developer tab: Navigate to the Developer tab on the top ribbon to access the macro tools.
- 3. Launch the VBA Editor: Click on 'Visual Basic' to open the editor, insert a module, and paste your daily logging script.
- 4. Run your Macro: Save your changes and click 'Macros' from the Developer tab to run the daily log script whenever needed.
Frequently Asked Questions
Why can't I use a standard formula to record daily historical data?
Standard Excel formulas are dynamic and recalculate automatically whenever source data changes. To freeze and permanently store a historical value from a specific day, a VBA macro is required to capture it and paste it as a static value.
How do I make the macro run automatically every day?
You can call your macro from the Workbook_Open event. By double-clicking 'ThisWorkbook' in the VBA editor and entering the 'Private Sub Workbook_Open()' code to call your macro, the script will trigger automatically every time you open the file for the day.
Why is my macro not saving the data into the correct date column?
Ensure that the date format in your lookup row matches the system's current date format recognized by the 'Date' function in VBA. Additionally, verify that the target cell in the historical row is completely blank, as the macro is designed to skip cells that already contain data.
