Recording time when a cell is copied in excel

[Guest]

Hi,
How do i record time in a cell (A1) when data which is present in another
cell (B1) is copied.
I am trying to create a sheet, when we use the shortcut key to copy (Ctrl +
c) the time is automaticallly in the column corresponding to the row.
Regards
Sudhir

 

[JE McGimpsey]

One way:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Activate()
Application.OnKey "^c", "SpecialCopy"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "^c"
End Sub

Put this in a regular code module (in the VBE, Insert/Module):


Option Private Module
Private Sub SpecialCopy()
Dim rTarget As Range
Dim rCell As Range
If TypeOf Selection Is Range Then
Set rTarget = Intersect(Selection, Range("B:B"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
With rCell.Offset(0, -1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
Next rCell
End If
End If
Selection.Copy
End Sub

 

[Bernie Deitrick]

Sudhir,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP

Public B1Selected As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$1" Then
B1Selected = True
Exit Sub
End If
If B1Selected And (Target.Address <> "$B$1") And (Application.CutCopyMode = xlCopy) Then
B1Selected = False
Application.EnableEvents = False
Range("A1").Value = "B1 was last copied on " & Format(Now, "mm-dd-yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

 

[Bernie Deitrick]

My bad - I missed the part about "Corresponding to the row" and thought that you only wanted B1.

The code below will not be restricted to Ctrl-C - it will work with any style copying.

HTH,
Bernie
MS Excel MVP


Public BSelected As Boolean
Public SelAdd As String
Dim myCell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
BSelected = True
SelAdd = Target.Address
Exit Sub
End If
If BSelected And (Target.Address <> SelAdd) And (Application.CutCopyMode = xlCopy) Then
BSelected = False
Application.EnableEvents = False
For Each myCell In Intersect(Range("B:B"), Range(SelAdd)).Cells
myCell.Offset(0, -1).Value = myCell.Address(False, False) & " was last copied on " _
& Format(Now, "mm-dd-yyyy at hh:mm:ss")
Next myCell
Application.EnableEvents = True
End If
End Sub

 

[Guest]

Hi,

I have tried with this code and nothing seems to be working right. Maybe i
am missing out on something.

I have entered the data .I have also put in the code as per what you have
mentioned. Howvere when i am pressing the ctrl c, nothing seems to be
happening but for the cell getting copied.

Regards

 

[Guest]

Hi Bernie,

Thanks a lot. this is exactly what i needed and it works just fine.

Thanks For your help and have a wonderful day.

Regards
Sudhir

 

[JE McGimpsey]

More likely I was - I was assuming that the user would be switching
between sheets.

You could add this to the ThisWorkbook module:

Private Sub Workbook_Open()
If ActiveSheet.Name = "Sheet1" Then _
Application.OnKey "^c", "SpecialCopy"
End Sub

Private Sub Workbook_Activate()
If ActiveSheet.Name = "Sheet1" Then _
Application.OnKey "^c", "SpecialCopy"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^c"
End Sub


Which would fire the code that changes the keyboard shortcut when the
workbook is opened or switched back to from another workbook.