Automatic time display on pasting

vcsush

Board Regular
Joined
Oct 1, 2006
Messages
52
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
How are you doing the copying/pasting?
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Larry

Why not just use the VBA Now function?

There's no need to insert a formula then convert it to a value.
Code:
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

Board Regular
Joined
Jun 1, 2003
Messages
130

ADVERTISEMENT

Norie:

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

I'm a cowboy coder, I guess.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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 :pray:

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

Richard :devilish:
 

vcsush

Board Regular
Joined
Oct 1, 2006
Messages
52
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
 

Forum statistics

Threads
1,141,721
Messages
5,708,092
Members
421,546
Latest member
delatollas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top