Error Running Copy VBA Code on Workbook_Open

[Bentley001]

I'm trying to run the code below to copy updated data from another file but am getting a 'Subscript out of Range' error on the line where it is referencing the tab on the source file (Sheets("Source File Tab").Select). The code runs fine when attached to a macro driven button. Confused on why it's not working?

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = False

ThisWorkbook.Activate
Sheets(4).Select
Sheets(4).Cells.ClearContents

Workbooks.Open Filename:="Path to Source File.xls", ReadOnly:=True

Workbooks("Source File Name.xls").Activate
Sheets("Source File Tab").Select
[a1].CurrentRegion.Copy

ThisWorkbook.Activate

[D1].PasteSpecial Paste:=xlPasteValues
[a1].Select

Workbooks("Source File Name.xls").Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheets("Sheet1").Select

End Sub

 

[dmt32]

Hi,
try running code from a standard module.

not tested but see if this update helps:

standard module:

Sub CopyData()
    Dim wbSource As Workbook
    Dim wsDest As Worksheet
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    On Error GoTo myerror
    Set wsDest = ThisWorkbook.Sheets(4)
    
    wsDest.Cells.ClearContents
    
    Set wbSource = Workbooks.Open(Filename:="Path to Source File.xls", ReadOnly:=True)
    
    wbSource.Sheets("Source File Tab").Range("A1").CurrentRegion.Copy
    
    
    wsDest.Range("D1").PasteSpecial Paste:=xlPasteValues
    
    wbSource.Close False
  
myerror:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .CutCopyMode = False
    End With
    
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Thisworkbook Code Page:

Private Sub Workbook_Open()
    CopyData
End Sub

You will need to ensure that the Path / File name & sheet name shown in RED are valid - update as required.

Dave

 

[Bentley001]

OMG, GOD does exist...thank you!...that code did the trick.