Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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 In article , sudhiramin wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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 "sudhiramin" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "sudhiramin" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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 "JE McGimpsey" wrote: 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 In article , sudhiramin wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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 "Bernie Deitrick" wrote: 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "sudhiramin" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recording time when a cell is copied in excel
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. In article , sudhiramin wrote: I have tried with this code and nothing seems to be working right. Maybe i am missing out on something. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time format in a cell getting copied automatically into another ce | Excel Discussion (Misc queries) | |||
Recording elapsed time | New Users to Excel | |||
recording more than 2 changing values in Excel at a time??? | Excel Worksheet Functions | |||
recording negative time | Excel Discussion (Misc queries) | |||
Recording time in Excel | Excel Discussion (Misc queries) |