Macro - time stamp pasted in next empty cell



I currently use a spreadsheet for tracking time and I press button, and the "now()" time is recorded in cell A2.  I would like to add to the macro so with each "click" of the button, the now() time is recorded in cell A2, but then pasted into cell C2.  On the next click, which would be somewhere between 30min to 2 hours later, I would like that time recorded in cell A2 again, and then pasted into cell D2.  Each time, i want the new time recorded into the next empty cell going to the right from cell C2.  I basically want to save the time each time the button is pressed.  I want to do this by pasting into the next empty cell.  It may be pressed twice a day, and it may be pressed 10times a day.



Welcome! Are you sure you don't want to save the data going down the column instead of across a row?
cappymer1 (rep: 120) Mar 19, '17 at 10:34 pm

Thanks for the welcome!

yes, I definitely want to go to the right, across and not down.  Your code below is what I was looking for.  I modified it a little to copy and paste a cell value as I don't want the "NOW()" calculation to change once it has been logged.  I am basically stopping and starting a task.   So I am recording the time of day each time "Stop" is pressed.

Thanks very much for your help!
enaud (rep: 2) Mar 20, '17 at 8:48 am
Add to Discussion


Selected Answer


To make the data go to the right you need to use a macro that finds the next empty cell.

If IsEmpty(Range("C2").Value) Then

    Range("C2").Value = Now()


    columnInput = Cells(2, Columns.Count).End(xlToLeft).Column
    Cells(2, columnInput + 1).Value = Now()

End If

This is the code that will input the data into cell C2 and then to the right after that.

I hope this can help!


Answer the Question

You must create an account to use the forum. Create an Account or Login