Inserting a date when another cell is automatically updated

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to update a sheet with values from another sheet when they are complete. I want the value to be updated into the other sheet and another cell needs to be populated with the current date. I then need that value to stick and not re-update when the date changes. Any ideas - this is really breaking my head?
 
1. How does XL know when a value is complete?

2. Where should the value be transferred to in the "other sheet"?

3. Where should the datestamp then go (i.e., where is "another
cell")? Is it the same cell every time, or does it vary?
 
----- J.E. McGimpsey wrote: -----

1. How does XL know when a value is complete?
When cell A1 in sheet 1 is populated with any numeric the value is copied to A1 in sheet 2. I then need to datestamp
another cell to show when the numeric value was transferred. The ss is getting too big for me to manually add dates.
This needs to be done each time another numeric is copied to sheet 2.
 
One way:

Put this in the Sheet1 Worksheet code module (right-click on the
worksheet tab and select View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1")) Is Nothing Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
With Worksheets("Sheet2").Range("A1")
.Value = Target.Value
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
End With
Application.EnableEvents = True
End If
End If
End With
End Sub
 
Back
Top