Solution has been found!
The code below resides on the SOURCE spreadsheet:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wbTarget As Workbook
Dim wbThis As Workbook
'Define Variables
Set wbThis = ActiveWorkbook
'Go to "DATA"
Sheets("DATA").Select
'Exit if "A2" is BLANK
Range("A2").Select
If IsEmpty(ActiveCell.Value) Then
ActiveWorkbook.Close True
Exit Sub
End If
If IsFileOpen("K:\FILENAME.xlsm") = True Then
MsgBox "Master Data Spreadsheet is already open - Your input data has not been updated. Don't worry. Data will be updated next time you open the form.", vbExclamation, "CANNOT UPDATE MASTER"
ActiveWorkbook.Close True
Exit Sub
Else
Set wbTarget = Workbooks.Open("K:\FILENAME.xlsm")
End If
'Clear Memory
Application.CutCopyMode = False
'Return to Source
wbThis.Activate
'Cut Rows
Range("A62236").Select
Selection.End(xlUp).Select
intLastRow = ActiveCell.Row
Range("$A$2:$S$" & intLastRow).Cut
'Back to Target Workbook
wbTarget.Activate
'Run Code "pastedata" from MasterList
Application.Run ("CIRMasterList.xlsm!pastedata")
End Sub
This checks to see if the Target Workbook is open, copies from the Source Workbook...and then calls the following code - pastedata() - from the Target:
Sub pastedata()
'Find Next Empty Cell
Range("A62236").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveWorkbook.Close True
End Sub
Hoping this may be able to help someone else out in the future.
Peace.
Bookmarks