Macro to run on timer errors out on the second cycle

[BigShanny]

Hello again,

I have the following code within ThisWorkbook to run a Macro on a timer. The Macro runs successfully upon opening the file, but errors out when it tries to run again in the designated time frame (every 10 seconds while I'm testing the code). Where am I going wrong here? I'd like it to copy the data from "Client Issues Log.xlsx" and paste it into the designated worksheet within "Test_Master_CMTimeTracker.xlsm" repeatedly on the set timer increment.

Dim RunTimer As Date

Private Sub Workbook_Open()

Call ImportData

End Sub

Sub ImportData()

RunTimer = Now + TimeValue("00:00:10")

Application.OnTime RunTimer, "ImportData"
MsgBox "10 Sec timer has started.", vbInformation


Call OpenClientIssuesLog

Workbooks("Client Issues Log.xlsx").Worksheets("Sheet1").Range("A2:P5").Copy _
    Workbooks("Test_Master_CMTimeTracker.xlsm").Worksheets("Client Issues Log").Range("A2")
  
Call CloseClientIssuesLog
  

End Sub

Sub OpenClientIssuesLog()

Workbooks.Open "C:\Users\LarryMac\OneDrive - LIC\HomeDrive_USURB61NS2VF1_home\Time Tracker\Client Issues Log.xlsx"

End Sub

Sub CloseClientIssuesLog()

Workbooks("Client Issues Log.xlsx").Close

End Sub

This is the error message I get:

 

[DanteAmor]

Try this way:

Dim RunTimer As Date

Private Sub Workbook_Open()
  Call ImportData
End Sub

Sub ImportData()
  Application.ScreenUpdating = False
  Call OpenClientIssuesLog
  Workbooks("Client Issues Log.xlsx").Worksheets("Sheet1").Range("A2:P5").Copy _
      Workbooks("Test_Master_CMTimeTracker.xlsm").Worksheets("Client Issues Log").Range("A2")
  Call CloseClientIssuesLog
  DoEvents
  Application.ScreenUpdating = True

  RunTimer = Now + TimeValue("00:00:10")
  Application.OnTime RunTimer, "ImportData"
End Sub

Sub OpenClientIssuesLog()
  Dim sPath As String
  sPath = "C:\Users\LarryMac\OneDrive - LIC\HomeDrive_USURB61NS2VF1_home\Time Tracker\"
  Workbooks.Open sPath & "Client Issues Log.xlsx"
End Sub

Sub CloseClientIssuesLog()
  Workbooks("Client Issues Log.xlsx").Close
End Sub

 

[John_w]

Change:

Application.OnTime RunTimer, "ImportData"

to:

Application.OnTime RunTimer, "ThisWorkbook.ImportData"

 

[BigShanny]

Change:

Application.OnTime RunTimer, "ImportData"

to:

Application.OnTime RunTimer, "ThisWorkbook.ImportData"

Thank you. Simple update and it works great.

 

[BigShanny]

Try this way:

Dim RunTimer As Date

Private Sub Workbook_Open()
  Call ImportData
End Sub

Sub ImportData()
  Application.ScreenUpdating = False
  Call OpenClientIssuesLog
  Workbooks("Client Issues Log.xlsx").Worksheets("Sheet1").Range("A2:P5").Copy _
      Workbooks("Test_Master_CMTimeTracker.xlsm").Worksheets("Client Issues Log").Range("A2")
  Call CloseClientIssuesLog
  DoEvents
  Application.ScreenUpdating = True

  RunTimer = Now + TimeValue("00:00:10")
  Application.OnTime RunTimer, "ImportData"
End Sub

Sub OpenClientIssuesLog()
  Dim sPath As String
  sPath = "C:\Users\LarryMac\OneDrive - LIC\HomeDrive_USURB61NS2VF1_home\Time Tracker\"
  Workbooks.Open sPath & "Client Issues Log.xlsx"
End Sub

Sub CloseClientIssuesLog()
  Workbooks("Client Issues Log.xlsx").Close
End Sub

Thank you.