I need to call a worksheet from inside a macro. The second workbook has an auto macro that runs a timer function to execute a closure . However I do not want that to run when I call it from the macro, only if I open the workbook manually. How can I add someway to break the timer loop in the calling of it from the first macro:
First macro call section
Auto timer macro in workbook
First macro call section
Code:
'Removed Excused JOBS
Dim srcWB As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, x As Long
Set desWS = ActiveWorkbook.Sheets("Open Vendor Jobs")
LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim Rng As Range, RngList As Object
Set RngList = CreateObject("Scripting.Dictionary")
Set srcWB = Workbooks.Open("Z:\EDS\ExcludedWo.xlsm")
Set srcWS = Sheets("EXCWOS")
For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next Rng
For x = LastRow To 2 Step -1
If RngList.Exists(desWS.Cells(x, 1).Value) Then
desWS.Rows(x).EntireRow.Delete
End If
Next x
' Close Workbook
Application.DisplayAlerts = False
Workbooks("ExcludedWo").Close (False)
Application.DisplayAlerts = True
Auto timer macro in workbook
Code:
Sub SaveWb()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
' Application.OnTime Now + TimeValue("00:0:30"), "SaveWb"
End Sub
(In this workbook)
Private Sub Workbook_Open()
'Date_Trim()
Sheets("EXCWOS").Select
ActiveSheet.Unprotect "EDS"
'Sort
Columns("A:D").Select
ActiveWorkbook.Worksheets("EXCWOS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("EXCWOS").Sort.SortFields.Add Key:=Range("A2:A17") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("EXCWOS").Sort
.SetRange Range("A1:D17")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C2").Select
'Add forumla
lr = Range("A1").End(xlDown).Row
If lr > 100000 Then GoTo xit
If lr = 2 Then On Error Resume Next
Range("C2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=DAYS(RC[-2],RC[-1])"
Range("c2:D2").Select
Selection.AutoFill Destination:=Range("c2:D" & lr), Type:=xlFillDefault
Range("a1").Select
'Trim old Entries
For lr = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("D" & lr).Value < "1" Then Rows(lr).EntireRow.Delete
Next
Sheets("EXCWOS").Select
ActiveSheet.Protect "EDS"
'MsgBox ("You have two minutes to update this sheet before it closes after you press enter to continue")
'Timer to close book
Application.OnTime Now + TimeValue("00:1:30"), "SaveWb"
xit:
Sheets("EXCWOS").Select
ActiveSheet.Protect "EDS"
End Sub