Copy system

[dave]

Hello guys,

I need a help here. I need a VB formula for copy. When i entered data
in A1 till A1000, i want to system time to be copied into next cel
which is B1. What formula should i use?

Thank you.

Dave

 

[Don Guillett Excel MVP]

Hi Dave

No formula. VBA. Rightclick sheet tab, "view code", paste:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
    If .Column = 1 And .Row <= 1000 Then
        Cells(.Row, 2).Value = Now
    End If
End With
End Sub

HTH. Best wishes Harald









- Show quoted text -

Or, for JUST the time use Time instead of Now

 

[Don Guillett Excel MVP]

Don, I know that OP said 'system time', but in which context would a
dateless time stamp make sense?

Best wishes Harald

Just giving OP options.

 

[dave]

Hi Dave

No formula. VBA. Rightclick sheet tab, "view code", paste:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
    If .Column = 1 And .Row <= 1000 Then
        Cells(.Row, 2).Value = Now
    End If
End With
End Sub

HTH. Best wishes Harald

Hi Harald,

That is excellent solution but if I used NOW(), all the cell time keep
change according to the system time. That why I want the time to be
copied. So that it will be static. Any solution?

 

[Gord Dibben]

Please re-read and re-copy Harald's code to your sheet module.

Does not use volatile worksheet function NOW()

Cells(.Row, 2).Value = Now returns a static date/time


Gord Dibben MS Excel MVP