Entering or pasting static time data from one sheet to another

[QuestaTH]

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!

[Leith Ross]

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.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim Cell As Range
  Dim DayRow As Long
  Dim Rng As Range
  
    If Target.Cells.Count > 1 Then Exit Sub
    
    Set Rng = Range("E8,G8,I8,L8,N8,Q8,S8")
    DayRow = Day(Now()) + 1
    
    If Not Intersect(Target, Rng) Is Nothing Then
       Application.EnableEvents = False
         For Each Cell In Rng
           Worksheets("Log").Cells(DayRow, Cell.Column).Value = Cell.Value
         Next Cell
       Application.EnableEvents = True
    End If
    
End Sub

Module2 Code to Auto-Update the Date and time

Sub AutoUpdateOn()
  Worksheets("Punch").Range("W2") = Now()
  Worksheets("Punch").Range("E8") = Now()
  Application.OnTime Now() + TimeValue("00:0:01"), "AutoUpdateOn", , True
End Sub

Sub AutoUpdateOff()
  On Error Resume Next
  Application.OnTime Now(), "AutoUpdateOn", , False
End Sub

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Call AutoUpdateOff
End Sub

Private Sub Workbook_Open()
  Call AutoUpdateOn
End Sub

[QuestaTH]

Awesome...thank you, Leith!

[Leith Ross]

Hello QuestaTH,

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

[QuestaTH]

Hi Leith,
One more question, if you don't mind. On the "Punch" tab, how can I clear the punch button results (cells G8,I8,L8,N8,Q8,S8) from the previous day so that the user starts with a blank sheet upon first opening on the current day? Maybe something that resets if the button macros were run pre-midnight?
Thanks again,
Tom

[Leith Ross]

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

Sub ClearPunchTimes()

  With Worksheets("Punch")
    .Range("G8,I8,L8,N8,Q8,S8").ClearContents
  End With
  
End Sub

Workbook_Open Event

Private Sub Workbook_Open()
  Call ClearPunchTimes
  Call AutoUpdateOn
End Sub

[QuestaTH]

Thanks for the quick response! Is there a way to clear the punches from the previous day, based on the current date, without clearing them each time the workbook is opened? Users would be opening and closing the workbook to punch in and out throughout the day, so I would like to be able to retain the current day's punch results. Also, it appears that only punches during the currently open session would be logged onto the "Log" tab, so if the file is closed the previous punches for the day would not be saved.
Thanks again in advance.
-Tom

[Leith Ross]

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.

[QuestaTH]

Perfect...thanks again!

[QuestaTH]

Hi...back again...hope I'm not being a pest. Something strange is going on with the date, which I noticed yesterday and confirmed today. In the morning, cell E8 shows the current date, as shown in cell W2. But after noon (checked at 12:01 PM), E8 shows tomorrow's date while W2 shows the current date, and I can't figure out why. Somehow it's treating 12 PM as 12 AM? Thanks again in advance.

[Leith Ross]

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.

Sub AutoUpdateOn()
  Dim TimeNow As Double
  TimeNow = Now()
    With Worksheets("Punch")
      .Range("W2") = TimeNow
      .Range("E8") = Fix(TimeNow)
    End With
  DoEvents
  Application.OnTime Now() + TimeValue("00:0:01"), "AutoUpdateOn", , True
End Sub

[QuestaTH]

Hi Leith,
The date issue appears fine, but now it's clearing the latest punch times again when I re-open the workbook.
Thanks again....

[QuestaTH]

OK, trying again this morning, it is not clearing today's punch times. Not sure what was going on yesterday afternoon, but I'll let you know if I notice the problem again. Otherwise, it seems to be working.
Thanks!
Tom

[Leith Ross]

Morning Tom,

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

[QuestaTH]

Hi Leith,
OK, it appears to have something to do with the time of day again. Punches were saved this morning, but as soon as I opened it after 12 PM, it cleared them out again... What do you think?
Thanks,
Tom

[Leith Ross]

Hello Tom,

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

[Leith Ross]

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.

[QuestaTH]

Hi Leith,
Thanks again...yes, this has been a learning experience. Did you mean to attach the revised file or do you want to wait and see how it behaves first?
-Tom