Selected Answer
Hi Rene and welcome to the Forum
I assume you want to copy all data from B to E when the time equals the time in E1 (06:30 or whatever).
In the attached workbook, I've modified code from Don's tutorial Countdown Timer in Excel to create a clock.
In column M you'll see two buttons- "Start timer" starts that clock (by running macro SetTime below). That puts the current time in cell M1 and (every second) calls the sub CheckTime which sees if the cuurent time matches E1 (provided there;s a value there). If so, it copies B2:B15 values to E2 to E15 and puts a comment in F1 to say it happened. It also beeps. If not, it does nothing but SetTime gets triggered after another second (and the random values in B2:B15 change- to emulate your varying values).
The code below is commented for your guidance:
Dim SchedRecalc As Date
Sub SetTime()
CheckTime 'see if action is needed
' set next time, locking to nearest second
SchedRecalc = WorksheetFunction.MRound(Time, TimeValue("00:00:01")) + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "SetTime" 'retrigger this macro after a second
Sheet1.Range("M1").Value = Time 'update time in A1
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="SetTime", Schedule:=False
End Sub
Sub Reset()
Call Disable 'stop timer
Sheet1.Range("M1").Value = "" 'clear time
Sheet1.Range("F1").Value = "" 'clear last copy comment
End Sub
Sub CheckTime()
With Sheet1.Range("E1")
'Check if current time matches E1 (with 0.5 seconds)
If IsEmpty(.Value) = False And _
Time >= .Value And _
Time < (.Value + TimeValue("00:00:01") / 2) Then
'if so,copy values
Sheet1.Range("E2:E15").Value = Sheet1.Range("B2:B15").Value
' add a comment in F1
.Offset(0, 1).Value = "Last copied: " & Now
' make a sound
Beep
End If
End With
End Sub
The button "
Stop timer" stops the clock and clears the comment in F1.
You can change the value in E1 at any time (for test purposes)
Hope this is what you wanted (or you can modify it to do what you want).