Automatically Timestamp Data Entries in Excel

MK25

In the tutorial video Automatically Timestamp Entries in Excel at about 9 minutes, Don says that the macro works when a cell is changed in the "data table", defined as a Range called myTableRange and set to A2:D10.

Just after 14 mins 30 sec into the video, he suggests that you add extra rows to that by changing the range.

To do extend the date timestamping to cover from row 3 up to E1000 and F1000 say, in the VB Explorer, just edit the Sheet1 macro to change A2 to A3 and D2 to D1000- see the change in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A3:D1000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("E" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("F" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

'Turn events back on
Application.EnableEvents = True
End Sub

Select the cell where you want to insert the timestamp.

Use the keyboard shortcut Control + :
This would instantly insert the current date in the cell.

Control + Shift + :
This would instantly insert the current time in the cell.

Hope this is useful.

Regards,
Jerry