I wish to export data to another workbook. I have added a msgbox before close asking if I'm ready to export the data. When I select yes the data exports. The issue I'm having is that the workbook the data exports to is regularly being used. I want to add a feature to my code that will notify the user the data won't be exported as the workbook is open elsewhere. If the workbook is closed I would like the data to export.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Output As String Output = MsgBox("Are you ready to export the data?", vbYesNo, "Data Export") If Output = vbYes Then 'Summary Page (APD+MTC) 'APD Premium Worksheets("SEC 1 (APD)").Range("AJ" & Cells.Rows.Count).End(xlUp).Copy Workbooks.Open Filename:="S:\US Div\Accounts\Paramount\2017 Data\Summary\2017 Summary - Edit.xlsm" Workbooks("2017 Summary - Edit.xlsm").Worksheets("Input P").Activate Range("C18").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveWorkbook.Close SaveChanges:=True End If End Sub