Macro stopped working - no longer selects worksheet

[luvofgreen]

First time poster. If you need any additional information let me know. Difficult to google issue. Hoping for some guidance.

Running in Excel 365 on Win10. Code has been working for at least 3 years. Stopped working 2 weeks ago after HR person said her Excel kept on hanging. Determined there was a firewall issue causing the ODBC call within the program was being blocked and gave the appearance of hanging while waiting for a timeout. Fixed that issue. Because of the constant killing of Excel, figured the the data file (Employee Change) became corrupt, so I rebuilt a new one from scratch, copying each tab to a new file, thankfully started working again. The Employee Change file stopped working again today, but was used by a different HR person, different machine. Remoted into that HR's machine and didn't see any issues as file closed properly multiple attempts. Rebuilt the file again, no luck.

SCENARIO
I have a main Excel file (Orientation.xlsm refer to as O) that calls another file (Employee Change.xlsm refer to as EC), it gets the last used row in a worksheet and gathers the rows of applicable data in EC and then writes that data to a worksheet in O. Closes and saves EC. This has been working great for 3 years

Today, when O closes EC, there is Workbook_BeforeSave code that no longer works. Call from O is below, WkbkS2 references Employee Change

Code from O

WbkS2.Activate

'Worksheets("Sheet1").Select   'Test to see where code stops working this works
WbkS2.Close SaveChanges:=True

Code in EC

Sub Workbook_BeforeClose(Cancel As Boolean)

'Dim x As Variant
    x = MsgBox("Are you sure you want to quit?" & Chr(10) & Chr(13) & "Once closed updates will be sent to IS Account Management. ", vbYesNo)
    If x = vbYes Then
        Application.ScreenUpdating = True
        ActiveWorkbook.Worksheets("Sheet1").Select    'Test to see where code stops working which is in here
        Call SendUpdatesWhenClosing(Row, SR)
        ThisWorkbook.Save
    Else
        Cancel = True
    End If
End Sub

Any attempt to switch worksheets or select any cells fails to update in Workbook_BeforeClose section of code. I have code that follows that selects the last used cells on the worksheet and it returns row 1 because the correct worksheets does NOT get selected. Any help or guidance to resolve this headache will be greatly appreciated.

 

[NdNoviceHlp]

Hi luvofgreen and Welcome to the Board! Maybe give these a whirl...

WbkS2.Save
WbkS2.Close

or....

Application.DisplayAlerts = False
WbkS2.SaveAs "YourFullPath", FileFormat := 51
WbkS2.Close 
Application.DisplayAlerts = True

HTH. Dave