VBA & Macro Problems

How to Auto-Insert Today's Date When a Row Changes in Excel VBA

Huma Ashraf Ch Oct 14, 2026 871 views

Question details

A user needs an Excel macro to automatically insert the current date into a specific date column whenever a value in the corresponding row changes.

How to Auto-Insert Today's Date When a Row Changes in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Tracking spreadsheet updates dynamically without breaking the macro if a user adds, deletes, or moves columns within the sheet.
Observed behavior: Hard-coded column references in the VBA script write the date to the wrong column if a new column is inserted or existing columns are moved.

Before you start

Ensure your workbook is saved in a macro-enabled format (.xlsm) and that the target date column has a unique, unchanging header name before applying the VBA script.

Solution 1Recommended

Dynamically Locate the Target Column by Header Name

Avoid hard-coding column letters. Instead, use a script that finds the specific header string so the macro continues to work even if columns shift.

Hard-coding a column reference (like Column C) is unreliable for dynamic spreadsheets. By instructing VBA to look for a unique header on Row 1, the script will always deposit the date in the correct column regardless of layout changes.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor. In the Project Explorer on the left, double-click the specific worksheet where you want to track changes.

Set Up the Worksheet_Change Event

From the top dropdowns in the code window, select 'Worksheet' and then the 'Change' event to automatically trigger the code whenever a cell is modified.

Locate the Date Column Dynamically

Write a script to search row 1 for your unique date header (e.g., 'Date Modified'). Use the Application.Match function or a Range.Find method to determine its current column index.

Write the Date and Prevent Infinite Loops

Before modifying the cell, temporarily disable events using Application.EnableEvents = False. Write VBA's 'Date' function to the targeted cell in the found column, then set Application.EnableEvents = True to re-enable triggers.

Dynamically Locate the Target Column by Header Name

Header Consistency: The header name must remain exactly as typed in your VBA script. If a user renames the header, the macro will fail to locate the target column.

Solution 2

Use an Excel Table for Structured Referencing

Converting your data into an official Excel Table allows you to reference columns by their table headers natively in VBA.

Advanced Macros in WPS Office

Track Spreadsheet Changes Efficiently with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, making it easy to implement dynamic row-tracking scripts and automate repetitive data entry tasks without compatibility issues.

1. Launch WPS Spreadsheet: Download and open WPS Office, then load your .xlsm file containing the data you wish to track.
2. Enable Developer Tools: Navigate to the Ribbon menu to activate the Developer tab, granting you full access to the built-in VBA editor.
3. Implement Dynamic Macros: Paste your dynamically referencing Worksheet_Change VBA code and save your workbook for automated, reliable date-stamping.

Seamlessly compatible with Microsoft Office VBA code and .xlsm formats.Lightweight software architecture ensuring quick startup times for complex macro tasks.Easily manage structured references, dynamically shifting tables, and large datasets.Familiar user interface that requires no learning curve for Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA date macro break when I insert a new column?

If your VBA code contains a hard-coded column reference (like Range("C" & Target.Row)), inserting a new column pushes your intended data column to the right, but the macro continues writing blindly to column C. Using dynamic header lookups ensures the macro always finds the correct column.

How do I stop my Worksheet_Change macro from looping infinitely?

Writing new data via a Worksheet_Change event inherently triggers the event again. To prevent an endless loop, wrap your date-writing command with Application.EnableEvents = False before writing, and set it back to True immediately afterward.

Does a dynamic column macro still work if I change the column's header name?

No. The script specifically looks for the exact text string of your header. If you rename the header in your spreadsheet, you must also update the corresponding search string in your VBA code.

Excel & Data Problems