Forum Discussion

ishai_aharoni's avatar
ishai_aharoni
Copper Contributor
May 01, 2023

Macro stops working

I have a macro command that should perform an action every minute between certain hours, the command initially works as it should but after 8 times it stops working Although Excel stays open all the time.

Here is the code:

Private Sub Workbook_Open()
Sub CopyValue()
Dim NextRow As Long
If Time >= TimeValue("9:00:00") And Time <= TimeValue("17:45:00") Then
NextRow = Cells(Rows.Count, "Q").End(xlUp).Row + 1
Cells(NextRow, "Q").Value = Range("H2").Value
Cells(NextRow, "R").Value = Time
Application.OnTime Now + TimeValue("00:01:00"), "CopyValue"
End If
End Sub

I would love to know if I have some fault in the code.

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 01, 2023

    ishai_aharoni 

    I assume that the Workbook_Open line doesn't belong there, or that you forgot to post the code of that procedure.

    Apart from that, your CopyValue macro will write to the sheet that is active when the code runs, even if you have activated another sheet or even another workbook in the meantime. So the data could become scattered over multiple sheets. It'd be better to specify the workbook and worksheet explicitly.

Resources