VBA & Macro Problems

How to Auto-Record a Permanent Timestamp When Pasting Values in Excel

Jason MillerJason Miller Nov 10, 2026 871 views

Question details

The user needs to automatically generate a permanent date stamp on a different worksheet whenever values are pasted into specific cells.

How to Auto-Record a Permanent Timestamp When Pasting Values in Excel
Product
Excel
Device & OS
not provided
Scenario
Tracking data entry or updates by automatically recording the exact historical date of a paste event.
Observed behavior
A permanent date needs to be recorded beside a matching record on a different sheet, but standard formulas like TODAY() constantly update and fail to preserve the original date.
Before you start

Verify the exact range where your data will be pasted (e.g., Column B) and note the exact name of the destination sheet where the permanent date should be recorded.

Solution 1Recommended

Use a VBA Worksheet_Change Event

Implement a VBA macro to detect paste events and insert a static date, bypassing the volatile nature of standard date formulas.

Because formulas cannot reliably detect copy-and-paste events without recalculating dynamically, a VBA macro is required. The Worksheet_Change event listens for modifications in a specific range and writes a hardcoded date to your target sheet.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. In the Project Explorer pane on the left side, double-click the specific worksheet where you will be pasting the data.

2
Insert the Worksheet_Change Code

In the blank code window, set up a Private Sub Worksheet_Change(ByVal Target As Range) block. Define your monitored range using 'Set watchRange = Me.Range("B2:B100")'. Use 'If Not Intersect(Target, watchRange) Is Nothing' to check if the paste occurred in that range.

3
Disable Events and Write the Date

Add 'Application.EnableEvents = False' to prevent the macro from triggering an infinite loop when it writes data. Write the date to the second sheet using a command like 'Worksheets("Sheet2").Cells(cell.Row, "C").Value = Date', and then immediately restore 'Application.EnableEvents = True'.

4
Save as a Macro-Enabled Workbook

To ensure your automated timestamp works in the future, go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown.

Static Timestamps: By using the VBA 'Date' function, the macro writes static text into the cell. This guarantees the timestamp will remain unchanged when you open the workbook the next day.
Use WPS Spreadsheet

Easily Manage VBA Macros and Timestamps in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to implement Worksheet_Change events to automatically record permanent timestamps across your sheets seamlessly.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open your existing data tracking document.
  2. 2. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor', or simply press Alt + F11.
  3. 3. Add the Automation Script: Double-click your target worksheet in the Project window and paste your Worksheet_Change macro to handle the paste events.
  4. 4. Save as Macro-Enabled: Click File > Save As, and select the .xlsm format to retain your automated timestamp functionality permanently.
Free and lightweight Office suiteFull compatibility with Microsoft Excel VBA macros (.xlsm)Familiar interface with a fully-equipped Developer tab
microsoft office alternative - wps office

Frequently Asked Questions

Why does the TODAY() formula change the date every day?

TODAY() is a volatile function designed to always display the current system date. It recalculates every time the worksheet is opened or an edit is made, which means it cannot store a historical or permanent date of a past event.

Can I record the exact time in addition to the date?

Yes. In your VBA code, replace the 'Date' function with the 'Now' function. 'Now' records both the current date and time. Ensure the destination cell is formatted to display Time (e.g., mm/dd/yyyy hh:mm AM/PM).

How do I ensure the macro only triggers for specific columns?

Use the Intersect function in your VBA code. By defining a specific watch range, such as 'Set watchRange = Me.Range("B:B")', the code will check if the target cell falls within Column B before executing the timestamp command.

Will the VBA code overwrite existing timestamps if I edit the cell again?

It will overwrite existing timestamps unless you add a condition. To prevent overwriting, wrap your timestamp command in an IF statement like 'If IsEmpty(Worksheets("Sheet2").Cells(cell.Row, "C")) Then' so it only writes a date if the cell is currently blank.