Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "MyMacro", , False
On Error GoTo 0
End Sub
If you pasted in the code into multiple modules or more than once in the same module you could get that error.
I've tested the code and it works fine for me.
The only tweak I would suggest is
Rich (BB code):Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime dTime, "MyMacro", , False On Error GoTo 0 End Sub
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End With
If Now - Date > TimeValue("14:00:00") Then Call StopIt
End Sub
Sub StopIt()
On Error Resume Next
Application.OnTime dTime, "MyMacro", , False
On Error GoTo 0
MsgBox "Timed macro stopped at " & Format(Now, "hh:mm:ss"), vbInformation
End Sub
Try
Rich (BB code):Public dTime As Date Sub MyMacro() dTime = Now + TimeValue("00:00:05") Application.OnTime dTime, "MyMacro" With Sheets("Sheet1") .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End With If Now - Date > TimeValue("14:00:00") Then Call StopIt End Sub Sub StopIt() On Error Resume Next Application.OnTime dTime, "MyMacro", , False On Error GoTo 0 MsgBox "Timed macro stopped at " & Format(Now, "hh:mm:ss"), vbInformation End Sub
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("15:00:00") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("Sheet2")
If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("16:00:00") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
'
'put the code for the other 22 sheets here
'
End Sub
I wouldn't have 24 timers running - that sounds like a recipe for confusion at best.
Try adapting this:
Code:Public dTime As Date Sub MyMacro() dTime = Now + TimeValue("00:00:05") Application.OnTime dTime, "MyMacro" With Sheets("Sheet1") If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("15:00:00") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("Sheet2") If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("16:00:00") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With ' 'put the code for the other 22 sheets here ' End Sub
I wouldn't have 24 timers running - that sounds like a recipe for confusion at best.
Try adapting this:
Code:Public dTime As Date Sub MyMacro() dTime = Now + TimeValue("00:00:05") Application.OnTime dTime, "MyMacro" With Sheets("Sheet1") If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("15:00:00") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("Sheet2") If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("16:00:00") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With ' 'put the code for the other 22 sheets here ' End Sub
Dear VoD:
Should I paste the last instruction you sent to me as a "Insert > Module" or in the first page?
Thanks
JC