Thread: Copy one cell to another every hour
View Single Post
 
Old 02-22-2019, 10:47 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
try this as the entire Modul1 code:
Code:
Option Explicit
Public dTime As Date

Sub ValueStore()
Dim NR As Long
NR = Range("I" & Cells(Rows.Count).Row).End(xlUp).Row + 1
Range("I" & NR).Value = Range("A1").Value
If NR > 30 Then
  Range("I2").Delete xlShiftUp
  Sheets("Blad1").ChartObjects("Diagram 4").Chart.FullSeriesCollection(1).Values = "=Blad1!$I$2:$I$30"
End If
Call StartTimer
 End Sub


 Sub StartTimer()
dTime = Now + TimeValue("01:00:00")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub StopTimer()
 On Error Resume Next
Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub
No Worksheet_Change event needed anywhere.


Your Start Timer button would be better calling ValueStore and not StartTimer, then you don't have to wait an hour for the first new value to appear.
Reply With Quote