How to Auto-Record a Permanent Timestamp When Pasting Values in Excel
Question details
The user needs to automatically generate a permanent date stamp on a different worksheet whenever values are pasted into specific cells.

- 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.
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.
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.
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.
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.
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'.
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.
Understand Why Dynamic Formulas Fail
Standard Excel functions recalculate automatically and are inherently unsuitable for permanent event timestamps.
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. Open your workbook: Launch WPS Spreadsheet and open your existing data tracking document.
- 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. 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. Save as Macro-Enabled: Click File > Save As, and select the .xlsm format to retain your automated timestamp functionality permanently.

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.




