VBA & Macro Problems

How to Log a Daily Excel Value and Timestamp with VBA

Tauseeq Magsi Oct 10, 2026 869 views

Question details

The user needs to create an Excel VBA macro that automatically copies a specific changing value and its timestamp into a separate log sheet at a scheduled time every day.

How to Log a Daily Excel Value and Timestamp with VBA

Product: Excel
Device & OS: not provided
Scenario: Creating an automated daily data logging system to track changing values over time by appending new data to the next available row.
Observed behavior: Users need a reliable method to find the next empty row, append data without overwriting, and correctly schedule the macro without relying on exact time-matching which often fails.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Excel or WPS Spreadsheet security settings before running the code.

Solution 1Recommended

Create and Schedule the Logging Macro with VBA

Use a VBA script to calculate the next available row on a log sheet and schedule the macro to run at a specific time using Application.OnTime.

Checking whether the current time exactly equals 10:00:00 often fails because the system may not run the macro at that precise second. Instead, it is highly recommended to use the Application.OnTime procedure to handle scheduling.

When referencing columns to find the last empty row, ensure you use a valid column reference like "A" instead of a specific cell address like "A47".

Set Up the Worksheets

Create a new worksheet in your workbook and name it "Log Sheet". Ensure your source data worksheet is named correctly (for example, "IR-House").

Open the VBA Editor

Press Alt + F11 to open the VBA Editor. Click on Insert > Module in the top menu bar to create a new module for your logging script.

Enter the Logging Code

Write your macro to copy the source values. Use the syntax: nextRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1 to accurately locate the next empty row on your Log Sheet.

Schedule the Macro

Use Application.OnTime to schedule the procedure. Add Application.OnTime TimeValue("10:00:00"), "YourMacroName" in your Workbook_Open event to trigger the log automatically.

Create and Schedule the Logging Macro with VBA

Avoid Cell Reference Errors: When using the ws.Cells() property in VBA, the column argument must be a column letter (e.g., "A") or a column index number (e.g., 1), not a cell address like "A47".

Automate Data Logging with WPS Spreadsheet Macros

WPS Office provides comprehensive support for VBA macros, allowing you to seamlessly automate your daily data logging, timestamps, and row-appending tasks using familiar code.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your Macro-Enabled Workbook (.xlsm).
2. Access the Developer Tools: Go to the Developer tab on the top ribbon menu and click on the 'VBA Editor' icon.
3. Insert and Run your Macro: Insert a new module, paste your logging script, and run it directly or attach it to a custom button in your spreadsheet.

Fully compatible with Microsoft Excel VBA macro syntaxLightweight and fast execution for scheduled scriptsFree to download with a highly familiar user interfaceSeamlessly manage and save Macro-Enabled Workbooks (.xlsm)

microsoft office alternative - wps office

Frequently Asked Questions

Why is my macro overwriting the previous log entry instead of appending?

This happens if the VBA code does not correctly calculate the next available row. Ensure you are using 'Cells(Rows.Count, "A").End(xlUp).Row + 1' to target the first empty row below your existing data.

Why did I get an error using "A47" in the Cells property?

The Cells(row, column) property requires a specific column letter (like "A") or number (like 1). Using "A47" references a specific cell, which is invalid as a column argument.

Can I trigger the macro automatically when the workbook opens?

Yes, you can place the Application.OnTime trigger inside the Workbook_Open event within the ThisWorkbook module in the VBA Editor. This ensures the schedule starts as soon as the file is opened.

Will the scheduled macro run if the Spreadsheet application is closed?

No, the application must remain open and running in the background for the Application.OnTime method to execute the scheduled macro at the designated time.

Excel & Data Problems