How to record paste value in a macro without referencing a particular cell

Question

I want to set up a macro that will add the current date/time to an already selected cell as a time stamp - the date or time will not update once it captures the current date and time. The macro should work on whatever cell has been selected.

Answer 1

Dear Petrina O Donnell, 

Good day! Welcome to Microsoft Q&A forum! 

Based on your description, I understand that you're trying to create a macro that inserts a timestamp (current date and time) into whichever cell is currently selected, without referencing a fixed cell or updating the value afterward.

Thanks for your question! The scenario you’re working on involves creating or customizing VBA macros, which typically requires development‑level guidance.

To get the most accurate, in‑depth help from experts who specialize in Excel scripting, I recommend posting this question in one of these communities:

• StackOverflow – very active community for VBA and macro‑related questions
  (Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link)
• Microsoft Tech Community – Microsoft Engineers and MVPs monitor and respond

These forums are designed for technical coding support and will give you broader visibility and faster responses from specialists.

Here, we mainly focus on troubleshooting Microsoft 365 product behavior rather than writing or reviewing custom code.

Thank you for your understanding, and feel free to reach out here if you have any additional Microsoft 365 issues we can assist with.

Wishing you a wonderful day ahead!

---

If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

To create a macro that adds the current date and time to the currently selected cell in Excel, you can use the following VBA code. This code captures the date and time when the macro is run and places it in the active cell without referencing a specific cell:

Sub AddTimestamp()
    ActiveCell.Value = Now()
End Sub

Explanation:

• ActiveCell.Value = Now() sets the value of the currently selected cell to the current date and time. The Now() function retrieves the current date and time.
• This macro works on whatever cell is selected at the time of execution, so it will not update the timestamp once it is set.

You can record this macro by following these steps:

1. Open Excel and go to the Developer tab.
2. Click on Record Macro and give it a name (e.g., "AddTimestamp").
3. Perform the action of entering the above code in the Visual Basic for Applications (VBA) editor.
4. Stop recording the macro.

Now, whenever you run this macro, it will insert the current date and time into the selected cell.

Answer 3

If you want to replace the current cell contents with the time stamp, you can use

Sub Stamp_Now()
    Selection.Value = Now()
End Sub

and you format the cell to meet your needs.'

If you want to append the time stamp to the current value, you can use

Sub Add_Stamp()
Dim Stamp As String
    Stamp = Format(Now(), "hh:mm")
    Selection.Value = Selection.Value & " - " & Stamp
End Sub

and you change the quoted format string to match your needs. You can also change the quoted separator string.