Avoiding hard coding a path and error handling

Question

I am new to VBA and just can't seem to get my head around this. So I have a source excel file where I copy the necessary data from a selected worksheet and then paste it to a main workbook (everyday use) to the exact worksheet using my macro. What I would like to do is avopid hard coding a path of the source file so that if the source file name changes that it still finds and opens the necessary source file and gets the data that is required. Also I would like to implement error handling to ensure that the source file is present and can be opened before running the macro.

Sub MacroCopy()
Workbooks.Open "C:\Users\xxxx\OneDrive\Desktop\OneDrive_0000-00-00\Project\Sources\sourcefile.xlsx"

ActiveSheet.ListObjects("Sourcesheet").Range.AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues

Workbooks("sourceworkbook.xlsx").Worksheets("sourceworksheet").Range("B5:EO11332").Copy _
ThisWorkbook.Worksheets("targetworksheet").Range("B5")

Workbooks("sourceworksheet.xlsx").Close SaveChanges:=True
 
End Sub

Answer 1

A couple of ideas:

---

This code will attempt to open any file in the folder - which in your comments since, you suggest won't necessarily be correct.

Sub MacroCopy_v2()

    Dim fldr As String, fnm As String, wbSource As Workbook
    fldr = "C:\Users\xxxx\OneDrive\Desktop\OneDrive_0000-00-00\Project\Sources\"

    fnm = Dir(fldr & "*.xlsx")
    
    If fnm = "" Then
        MsgBox "No file found. Aborting."
    Else
        Set wbSource = Workbooks.Open(fldr & fnm)
        wbSource.ActiveSheet.ListObjects("Sourcesheet").Range.AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues
        wbSource.Worksheets("sourceworksheet").Range("B5:EO11332").Copy _
                            ThisWorkbook.Worksheets("targetworksheet").Range("B5")
        wbSource.Close SaveChanges:=True
    End If
    
End Sub

You might want to specify the sheet name, rather than use ActiveSheet in case there are multiple sheets available.

I'm slightly puzzled also by the SaveChanges:=True - as you're not changing the source workbook (as far as I can tell).

---

This code looks for a default filename and if that isn't present in the folder then it opens a prompt for the user to manually select a file.

Sub MacroCopy_v3()

    Dim fldr As String, defaultfnm As String, openpath As String, wbSource As Workbook
    fldr = "C:\Users\xxxx\OneDrive\Desktop\OneDrive_0000-00-00\Project\Sources"
    
    defaultfnm = "sourcefile.xlsx"
    
    openpath = fldr & "\" & defaultfnm
    If Dir(openpath) = "" Then
        ChDrive "C:"
        ChDir fldr
        openpath = Application.GetOpenFilename("Source file (*.xlsx),*.xlsx")
    End If
    
    If openpath <> "False" Then
        Set wbSource = Workbooks.Open(openpath)
        wbSource.ActiveSheet.ListObjects("Sourcesheet").Range.AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues
        wbSource.Worksheets("sourceworksheet").Range("B5:EO11332").Copy _
                            ThisWorkbook.Worksheets("targetworksheet").Range("B5")
        wbSource.Close SaveChanges:=True
    Else
        MsgBox "No file selected. Aborting."
    End If
    
End Sub

Detecting if a worksheet exists - you can find functions to perform that here:

Test or check if sheet exists

You could do something similar to detect if the ListObject exists also.