Hi All, I was hoping someone might be able to help with the following issue. I enter info into workbook1 and then run a macro to update workbook2 and workbook3. The code below works fine in terms of the updating but I am struggling to find a way to handle the problems if workbook2 or workbook3 is open.
I was hoping to check if workbook2 or workbook3 is open and then simply exit the sub. Thank you in anticipation of any help that can be provided. Best regards, Jay.
Private Sub Update_Click() On Error GoTo ErrHandler Application.ScreenUpdating = False 'Confirmation of update response box Dim Response As VbMsgBoxResult Response = MsgBox("Confirm you want to write this information to the relevant workbook!", vbYesNo, "Are you sure?") If Response = vbNo Then Exit Sub Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook Dim wb2Paste As Range, wb3Paste As Range On Error Resume Next Set wb1 = ThisWorkbook Set wb2 = Workbooks.Open(Filename:="C:\Users\Desktop\Workbook 2.xls") Set wb3 = Workbooks.Open(Filename:="C:\Users\Desktop\Workbook 3.xlsx") If wb2.ReadOnly Then MsgBox "Workbook 2 is already open. Please Try later!", vbCritical, "Workbook Open" Exit Sub End If If wb3.ReadOnly Then MsgBox "Workbook 3 is already open. Please Try later!", vbCritical, "Workbook Open" Exit Sub End If With wb2.Sheets(2) Set wb2Paste = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) End With 'Copy Data from Wb1.Sheet1 to Wb2.sheet1 wb1.Sheets(1).Range("A8:I38").Copy wb2Paste.PasteSpecial Paste:=xlValues, Transpose:=False Application.CutCopyMode = False Set wb2Paste = Nothing wb2.Close SaveChanges:=True wb1.Sheets(1).Range("A8:I38").ClearContents With wb3.Sheets(1) Set wb3Paste = .Range("A985") End With 'Copy Data from Wb1.Sheet2 to Wb3.sheet1 wb1.Sheets(2).Range("A8:L497").Copy wb3Paste.PasteSpecial Paste:=xlValues, Transpose:=False Application.CutCopyMode = False Set wb3Paste = Nothing wb3.Close SaveChanges:=True wb1.Save Application.ScreenUpdating = True Exit Sub ErrHandler: MsgBox "An error has occurred. If the problem persists use the email button on the home page to report the issue!", vbOKOnly + vbCritical, "Error Message" End Sub