Ontime Macro

[Himateja]

Can we restrict Application.OnTime Macro from applying to all workbooks open

Code in This workbook

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = True
ThisWorkbook.Save
Call StopTimer
Application.EnableEvents = True

End Sub
Private Sub Workbook_Open()
Application.Run "StartTimer"
End Sub
Code In Module 1
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
MsgBox "Successful"
End Sub

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub


Sub Save1()
Dim path1 As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Workbooks("WE-Final.xlsm").Worksheets("My_Subs").Activate
path1 = "\\ant.amazon.com\dept-as\hyd\hyd1\HR\NA-ERC\ERP Workallocation\Work Extraction- Asc Files\Associate Data\" & Environ("username") & ".xlsx"

If Len(Dir(path1, vbDirectory)) = 0 Then
Set wkb = Workbooks.Add
wkb.SaveCopyAs path1
End If

Set destwb = Workbooks.Open(path1)
Set dest_sheet = destwb.Worksheets("Sheet1")
Set currSheet = ThisWorkbook.Sheets("My_Subs")

dest_sheet.Cells.Delete
currSheet.Cells.Copy _
Destination:=dest_sheet.Cells
destwb.Save
destwb.Close
Worksheets("My_Subs").Activate
ThisWorkbook.Save
MsgBox "Saved"
StartTimer
End Sub

 

[bobsan42]

I cannot understand what excatly you mean. OnTime runs at Application level, regardles of workbooks. It just has to be able to find the Sub you told it to call.
What exactly is the issue?

 

[Himateja]

I have a macro which has to save for every 10 minutes. This Save Ontime applies to all open workbooks and reopens the sheet even after closing the sheet. So i've added a beforeclose event to stop, but the issue is this doesn't apply or seems to not work when the sheet that has the code is closed when other workbooks are left open.

 

[Himateja]

I cannot understand what excatly you mean. OnTime runs at Application level, regardles of workbooks. It just has to be able to find the Sub you told it to call.
What exactly is the issue?

Also if the Ontime triggers the save the beforeclose doesn't seem to work

 

[GWteB]

Within this part ....

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

... the RunWhen has local scope, so your OnTime schedule cannot and will not be canceled.

Since your previous question is very, very similar, take a look over there ...

OnTime Applying all open Workbook and reopening the Excel sheet which was closed

I have This in My ThisWorkbook Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Worksheets("My_Subs") Application.Run "CommandButton2_Click" End With End Sub Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:00:30"), "Save1" End Sub And...

www.mrexcel.com