MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copy from cell and insert in other cell automatically


Posted by Nicko on June 29, 2001 3:10 PM

I'm creating a time report in excel. I use 2 columns the first is the time I started the job, the second is the time ended. On the next row the time started is the same as the previous rows time ended. how can I automate this and stop entering the time twice in 2 columns (other than the time consuming copy and paste).

Thank you - A box of Mini-Muffins to the person with the beset solution.


Posted by Scott on June 29, 2001 3:42 PM

If you have the start time in A1, and the end time in B1, then you can enter in =B1 into A2 and copy it down:

A B
1 8:00 9:00
2 =B1
3 =B2

Posted by Joe Was on June 29, 2001 5:05 PM

Macro to the last value of B to the next empty A

If your ending time is in the last cell with a value in column "B" and
your next starting time is the last ending time, in the next empty row down of column "A" then:

Sub myTime()
'
' By Joseph S. Was

Dim myLast As Long
Range("B65536").End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
End Sub

I used a Hot-key (Macro - Macros - Options add your key, I used "t.")

I tested it with:

A B
1 2
2 3
3 4

and got:

A B
1 2
2 3
3 4
4

after pressing Ctrl-t
with the cursor ending up in column "B" next to the "4."

Is this what you wanted, let me know! JSW

Posted by Joe Was on June 29, 2001 5:08 PM

Remove the Dim its not needed! JSW

This is cleaner. JSW By Joseph S. Was