copy and paste based on a time in cell A1

[BenGrobler]

Hi Guys

I have a running clock in an excel worksheet, here is the code:
Dim SchedRecalc As Date
Sub Recalc()
With Sheet8.Range("A1")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
I need the value in cell E4 to be copied when the running clock reaches 09:00 am and paste it in D17
then when the clock reaches 10:00 am it must copy the value in E4 and paste it in D18...
i need to create a graph with this info..

Is there a vba code that i can insert it into the above to make it happen or a if statement to copy and paste??

Please help

 

[offthelip]

try this:

Sub Recalc()
With Sheet8.Range("A1")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
hr = Hour(Now)
If hr >= 9 Then
 Range("D17").Offset(hr - 9, 0) = Range("e4")
End If
Call SetTime
End Sub

 

[BenGrobler]

Thank you

How would the code look like for 10, 11, 12 etc until 16:00?

 

[BenGrobler]

I dont know if I am clear enough on this...
when it is 09:00 in the morning the value in Cell e4 is maybe 2580 then at 10:00 it is 4570
when the clock is getting to 09:00 cell e4 is copied and pasted in d17, at 10:00 e4 is copied again and pasted in d18 at 11:00 e4 is copied again and pasted in d19
this carries on until 16:00. the values in e4 will go up and down during the day and that is what i need to get done automatically onse the set time macro is running?

the code maybe is like this
if time in range a1 = 09:00 then range e4 .select
selection copy
range d17 . select
selection paste
end if
if time in range A1 = 10:00 then range e4 .select
selection copy
range d18. select
selection paste

 

[offthelip]

That is exactly what the code does, let me explain:

hr = Hour(Now)   ' this line takes the hour from the current time
If hr >= 9 Then   ' if the hour is greater than 9 it does the next lines
 Range("D17").Offset(hr - 9, 0) = Range("e4")   ' this line uses Hr ( the hour) to calculate the offset down from D17, so when hr =9, the offset is zero so it writes into D17, when hr=10 the offset is(10-9) =1
                                                                          ' so it writes in D18, when hr= 11 offset = 2 so it wrties into D19
End If

Note the code copies that values into the location for entire 1 hour, so if you want the first value in the hour change code to

hr = Hour(Now)
If hr >= 8 Then
 Range("D17").Offset(hr - 8, 0) = Range("e4")
End If

 

[BenGrobler]

Thank you for your help