Entering or pasting static time data from one sheet to another

There’s probably a relatively uncomplicated solution to this, but I’m new to macros don’t know how to pull all the pieces together. I’m trying to create a file that can be used as a timecard for each individual employee to use for punching in and out in real time, while separately creating a record of their in/out times that only a supervisor with password privileges can edit or clear (to prevent employees from altering their own times). I’ve already set up a “Log” worksheet with buttons to punch in/out with the current time (using“= Evaluate("=NOW()")) in multiple in/out columns on a single row that calculates current hours worked (file attached).

Now I want to have these “live” dates and times also logged statically on a second (password-protected) “Log” sheet in the same file. What I have in mind is a row for each day of the month on the “Log” sheet that compares the day of the month in “Log” column D with the date in “Punch” cell E8 and enters the dates and times for that day in the corresponding columns of the “Results” sheet, resulting in a static list of dates and times in and out.

The supervisor would save a copy of the “Results” sheet on a weekly or bi-weekly basis, then clear the dates/times from the original file so that the user can continue to use it without overwriting any needed data from the previous month.

So in short, I’m looking for something that will paste or enter values from one sheet to the other based on the date throughout the month.

Thanks in advance for any advice!

Hello QuestaTH,

Welcome to the Forum!

I made a few changes to your workbook. The data will now be logged based on the day of the month once the user has entered it. The other change was to provide you with a continuous data and time display. W2 now displays the data and the time in hours, minutes, and seconds. Cell E8 is also updated with this same value automatically.

Here is the macro that updates the "Log" sheet. This in the Worsheet_Change event for "Punch". The attached worksheet has all the changes I mentioned.

Please Login or Register  to view this content.

Module2 Code to Auto-Update the Date and time

Please Login or Register  to view this content.

Code for ThisWorkbook to Turn the Auto-Update On and Off

Please Login or Register  to view this content.

Hello QuestaTH,

Good to hear you like the changes. If you need any changes later, let me know.

Hello QuestaTH,

The punch times will now be clear when the workbook opens. I added a macro to Module2 and call to the macro in the Workbook_Open event module. All changes have been made to the attached workbook.

Module2 New Macro

Please Login or Register  to view this content.

Workbook_Open Event

Please Login or Register  to view this content.

Hello Tom,

The workbook now clears the punch data if the saved date is different from the current date. When the user closes the workbook, it will automatically be saved. I stayed up late to test it out. The latest edition is attached.

Hello Tom,

That was my fault. I was storing the date as an integer in E8 to remove the time portion. The problem was caused by a rounding error when converting the date to an integer value. The change in the code below should solve the problem. Copy the code below and paste it over the same Sub in Module2. If you have any problems, let me know.

Please Login or Register  to view this content.

Morning Tom,

Keep me updated on the clearing problem. It should be working correctly since the last change.

Hello Tom,

I am going to examine this in more detail. I'll post back later.

Hello Tom,

This is revamp of your code. Most of the formulas have been removed and event code has been used. I have observed this for a few days and am satisfied this version does what you want without problems.

Hello Tom,

I had been experiencing some odd behaviour and thought it might be my system. You confirmed that it isn't. This leads me to believe that there are some stability issues with the Application.OnTime method. Currently, I have changed the code some to help stabilize it. Time will tell if these changes hold. Thanks for being patient. This project has held many unexpected surprises.