Excel 2007 - Auto enter date in a copy of Wksht upon data entry 1

[BxWill]

Have a worksheet on Sheet 1 (10 columns and 20 rows) and a exact copy on Sheet 2

On Sheet 1, I enter data and would like the worksheet on Sheet 2 to record the date that a entry is made and/or updated on Sheet 1.

Is this possible?

If so, can anyone provide a starting point?

Currently experimenting with the following;

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Target.Offset(0, 1).Value = Format(Now, "mm dd yyyy h:mm:ss")
End If
End Sub

thanks in advance.

 

[SkipVought]

hi,

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
    Sheets("Sheet2").cells(Target.Row, "A").Value = Now
  End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

So, if I have 10 columns with 20 rows per column and I want to have a date stamp on Sheet2 for every cell on Sheet 1, what modifications to the code would be necessary? Maybe, columns A to J... instead of A:A??

 

[SkipVought]

Please describe the nature of the range in question.

What factors identify this range that would make it logically identifiable

Is this range static or dynamic?

In general...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, [b]Range(YourRange)[/b]) Is Nothing Then
    Sheets("Sheet2").cells(Target.Row, [b]Target.column[/b]).Value = Now
  End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Will try the code.

The workbook is saved as a shared workbook and the range is static.

The worksheet on sheet 2 is the exact same copy of the worksheet on Sheet 1.

Sheet 2 will be hidden and will function as a "Audit trail" to let me know when the employee updated data on sheet 1.

For example, if the employee entered data in cell location A10 on sheet 1, then cell location A10 on sheet 2 will contain the date and time that the cell location A10 on sheet 1 was updated.

 

[SkipVought]

That's what it does.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}