Automatic time display on pasting

[vcsush]

Hi All,
First up, thanks for any / all help.

I need to make a code for automatic time entry in a cell. Can't use now() because it updates every time I breathe. Anyway, thanks to all the guys here, I know that I need to code it in VB.

Now, I don't really know VB. I have tried to play around with it but to no avail. So would reallyyyyyyyyyy appreciate it if someone could spend a few minutes.

Here is the deal. I copy three columns from somewhere and paste them in A5. So, they get pasted in the A, B and C columns starting from the 5th row. Now, I need the time to be entered in the first cell which is the merge of A1, B1 and C1. Time should get entered automatically when I paste the three columns.

Now here is the tricky part......I need to do this every three columns. This means that I will repeat this with ABC, then DEF, GHI and so on. This will go on until the end of Sheet1 and then continue to Sheet2 and then Sheet3. This means that the time first needs to go into the merged cells A1,B1,C1 and then D1,E1,F1 and then G1,H1,I1 and so on across Sheet1, Sheet2, and Sheet3 WHEN I paste in those particular columns.

I would really appreciate if someone could throw me either the code or a hint or something. I am dying here.

Thanks for the time guys......

Vcsush

 

[Norie]

How are you doing the copying/pasting?

 

[larrydunn]

To put code in place:

Right-click on worksheet tab.
Select View Code.
You will see General in the left side pulldown. Bring down Worksheet.
Paste in the code.
Hit Alt-Q and start testing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sSelection As String
Dim sColumnLetters As String
Dim iDet As Integer

If Target.Row <> 5 Then GoTo EndOfSubroutine
sSelection = Target.Address
sSelection = Right(sSelection, Len(sSelection) - 1) 'Remove dollar sign

iDet = InStr(sSelection, "$")
sColumnLetters = Left(sSelection, iDet - 1)
'Insert and remove NOW formula
ActiveSheet.Range(sColumnLetters & "1").Formula = "=NOW()"
ActiveSheet.Range(sColumnLetters & "1").Value = ActiveSheet.Range(sColumnLetters & "1").Value
EndOfSubroutine:
End Sub

Note: this will have to go into each of your worksheets. If you're satisfied with the way it works, and if possible, it would be easiest to create one worksheet and make clone copies of it.

 

[Norie]

Larry

Why not just use the VBA Now function?

There's no need to insert a formula then convert it to a value.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row <> 5 Then Exit Sub
    
    Application.EnableEvents = False
    Cells(1, Target.Column) = Now()
    Application.EnableEvents = True

End Sub

 

[larrydunn]

Norie:

Thanks for the cleanup. Your code is elegant and does the job more smoothly.

I'm a cowboy coder, I guess.

 

[Richard Schollar]

I'm a cowboy coder, I guess.

So's Norie!!! Just look at those utterly unnecessary brackets:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row <> 5 Then Exit Sub

Application.EnableEvents = False
Cells(1, Target.Column) = Now()
Application.EnableEvents = True

End Sub


Just kidding ray:

And Larry, as to being a cowboy coder, you say it like it's a bad thing?!

Richard

 

[vcsush]

Thanks

Thanks for the help guys. Both the codes are great. I wish I could let you all know how grateful I am. This makes my job a lot easier.

Thanks a bunch guys.......................Much obliged.

Time to learn VB

Vcsush