VBA code will not run automatically when excel file is opened anymore

Question

I was able to open the excel file before and the copied sheet of a closed workbook would paste itself into the opened workbook, deleting the sheet that was titled "SEEBREZ IMS.1". But now when I open the excel file it wont run my vba code, it works efficiently if I manually run the code.

Sub WorkbookOpen_CopyandReplace()

'It will check to see if there is a worksheet wit the same name and delete it

'On Error GoTo 0 then excutes the next line of code that will allow us to enter in a workbook, with it being closed, and copy that worksheet in this workbook


Application.ScreenUpdating = False

    On Error Resume Next
        ThisWorkbook.Sheets("SEEBREZ IMS.1").Delete
    On Error GoTo 0
    
    Set closedBook = Workbooks.Open("P:\62001-5 IN PROCESS\DDE 62001-5\62001-5 4141-84 SEEBREZ SEAT_IMS.xls")
    closedBook.Sheets("SEEBREZ IMS.1").Copy Before:=ThisWorkbook.Sheets("IMS.1")
    closedBook.Close SaveChanges:=False
    
    
Application.ScreenUpdating = True


End Sub

Answer 1

A Workbook Open: Import Worksheet From Closed Workbook

ThisWorkbook Module

• This code can only be in the ThisWorkbook module and as indicated by Tim Williams in the comments, you cannot change its signature. This is an event code and it will run when something happens, in this case, each time the workbook is opened. There are many other events for ThisWorkbook and even more so for each sheet.

Option Explicit

Private Sub Workbook_Open()
    ImportSEEBREZ
End Sub

Standard Module e.g. Module1

• Such code usually goes into a standard module so you can call it using a simple ImportSEEBREZ from anywhere.
• Alternatively, you could move it to the ThisWorkbook module with the event code. If additionally, you don't want it to be seen in the Macro dialog, you can make it private (Private Sub...).

Option Explicit

Sub ImportSEEBREZ()
    Const PROC_TITLE As String = "Import SEEBREZ"
    On Error GoTo ClearError ' start error-handling routine

    ' Define constants.
    Const SRC_PATH As String = "P:\62001-5 IN PROCESS\DDE 62001-5\" _
        & "62001-5 4141-84 SEEBREZ SEAT_IMS.xls"
    Const COPY_SHEET As String = "SEEBREZ IMS.1"
    Const BEFORE_SHEET As String = "IMS.1"
    
    ' Reference the destination workbook.
    Dim dwb As Workbook: Set dwb = ThisWorkbook
    ' Reference the destination Before sheet.
    ' With 'As Object' and '.Sheets', I'm allowing it to be a chart.
    Dim bsh As Object: Set bsh = dwb.Sheets(BEFORE_SHEET)
    
    ' Attempt to reference the destination Copy worksheet.
    Dim cws As Worksheet
    On Error Resume Next ' prevent error if the worksheet doesn't exist
        Set cws = dwb.Sheets(COPY_SHEET)
    On Error GoTo ClearError ' continue error-handling routine
    
    Application.ScreenUpdating = False
    
    ' Check if a reference to the source Copy worksheet was created.
    If Not cws Is Nothing Then ' yes, exists
        Application.DisplayAlerts = False ' delete without confirmation
            cws.Delete
        Application.DisplayAlerts = True
    'Else ' no, doesn't exist; do nothing
    End If
    
    ' Open and reference the source workbook.
    Dim swb As Workbook: Set swb = Workbooks.Open(SRC_PATH)
    ' Reference the source Copy sheet.
    Dim sws As Worksheet: Set sws = swb.Sheets(COPY_SHEET)
    
    ' Copy the source Copy sheet before the destination Before sheet.
    sws.Copy Before:=bsh
    
    ' Close the source workbook without saving changes.
    swb.Close SaveChanges:=False ' it was just read (copied) from

ProcExit:
    On Error Resume Next ' prevent endless loop if error in the continuation
        If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
    On Error GoTo 0
    Exit Sub
ClearError: ' continue error-handling routine
    MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf _
        & Err.Description, vbCritical, PROC_TITLE
    Resume ProcExit
End Sub