Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro - time stamp pasted in next empty cell

0

Hi,

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.

excel
MS Office Version: 2010
posted by: enaud (rep: 2) on Sun Mar 19, 2017 at 1:52 pm
Answer
Discuss

Discussion

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
Hi,

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

Answers

0
Selected Answer

Welcome!

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()

Else

    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!

posted by: cappymer1 (rep: 120) on Sun Mar 19, 2017 at 10:45 pm
Discuss


Answer the Question

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