Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Date/Time Stamp in another sheet if cell value changes

0

Hello

I just watched your video on how to put timestamp in a cell if the value of another cell changes ( https://www.youtube.com/watch?v=gIPdx4-rqGg ).

My question is simple:

I want to put the date in a cell in "sheet2" whenever a cel is changed in "sheet1".

Example:

When I put information in cell "A1" in the sheet "Data", a timestamp will automatically appear in the cell "A1" in sheet "Date and Time".

I don't know how to set the destination of the date in another sheet.

I attached a simple example in excel.

Thank you for your time.

Best regards,

Pedro Silva

Answer
Discuss

Answers

0
Selected Answer

Hello, Pedro.

I hope this code can help you.

Option Explicit
Dim a As String

Private Sub Worksheet_Change(ByVal Target As Range)

    a = Target.Address
    Sheets("Date and Time").Range(a) = Now

End Sub

This code, takes the addres of the cell which is changed and put the timestamp in same addres but another sheet, this case "Date and Time". I attached the file.

Regards
Basilio

Discuss
0

Here is a "No frills" version for the code you asked for.

Private Sub Worksheet_Change(ByVal Target As Range)
      ' 011

    Dim Rng     As Range                ' Trigger range

    ' this procedure will not respond to multiple simultaneous changes
    '   as may be created by Paste or Delete actions.
    If Target.Cells.CountLarge > 1 Then Exit Sub

    ' trigger responds to any cell in the (one) table on this sheet
    '   DataBodyRange excludes the header and totals rows
    Set Rng = ActiveSheet.ListObjects(1).DataBodyRange
    If Not Application.Intersect(Rng, Target) Is Nothing Then
        With Folha2.Range(Target.Address)
            .Value = Now()
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"       ' change to suit
            .HorizontalAlignment = xlLeft
        End With
    End If
End Sub

Note that the target worksheet is addressed by it CodeName. This is easier syntax and it allows the user to change the name of the tab without the code having to be updated.

So, what would be the frills? There is no provision on what to do if there is a time stamp in the addressed cell already. The options range from simply over-writing the existing, as the code does now, to refusing to allow the entry or adding a note to the time stamp which records that a change was amde and who made it.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login