I am using the following code to copy changes to a cell value every 30 seconds. The code works, sort of, but has the following bugs;
When timevalue = 5 seconds it copies every 5 seconds but copies the change into 5 cells at a time. It will only fill 64 rows and then stops copying.
If I change the value to 30 seconds, it only copies to 1 row at a time but still will not copy more than 64 rows.
In each case the bottom of the screen still shows copying message at the time interval even though no copying is taking place.
Any help will be greatly appreciated.
When timevalue = 5 seconds it copies every 5 seconds but copies the change into 5 cells at a time. It will only fill 64 rows and then stops copying.
If I change the value to 30 seconds, it only copies to 1 row at a time but still will not copy more than 64 rows.
In each case the bottom of the screen still shows copying message at the time interval even though no copying is taking place.
Any help will be greatly appreciated.
Code:
Option Explicit
Public dTime As Date
Sub ValueStore()
Dim dTime As Date
Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
Call StartTimer
If Range("A1") > 275 Then Beep
End Sub
Sub StartTimer()
dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub