Oy.
New poster here, moderate VBA user. Begging for some help with a project that has had me stumped for the last 48 hours.
What I am attempting to do:
When closing a Workbook (named "CIR") I would like the data on the "DATA" tab to be cut and then pasted to another Workbook ("CIR Master") in the first blank row. Code posted below goes well until the last line.
The Issue:
Rather than pasting to the target Workbook ("CIR Master"), the code below pastes to the source workbook. I have tried many different ways of pasting, but no joy.
Any help would be greatly appreciated.
A second question...any ideas on what code could be used to see if the target Workbook is open? (ie, whether it would open as "read-only".)
'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
Exit Sub
End If
'Open CIR Master List
Set wbTarget = Workbooks.Open("K:\CIR Master List.xlsm")
'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
'Find Next Empty Cell
Range("A62236").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
'...and it FAILS here
ActiveSheet.Paste
Bookmarks