How to add 2 features to a file transfer macro.

1)
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

2) Untested.

Sub DRILL_XFER()
'
' DRILL PL TRANSFER MACRO
' TRANSFER DRILLS AND PLS DAILY DURING CLOSE
'
    Dim e, msg As String
    For Each e In Array(Array("H:\File1 - 2018-12-31.xls", "H:\File1 - 12-2018.xls"), _
                        Array("S:\File2.xls", "H:\File2 - 2018-12-31.xls"))
        If Dir(e(0)) <> "" Then
            If Not IsFileOpen(CStr(e(0))) Then
                FileCopy e(0), e(1)
            Else
                msg = msg & vbLf & e(0) & " is in use"
            End If
        Else
            msg = msg & vbLf & e(0) & " is not found"
        End If
    Next
    If Len(msg) Then
        Sheets.Add.Cells(1).Resize(UBound(Split(msg, vbLf)) + 1).Value = _
        Application.Transpose(Split(msg, vbLf))
    Else
        MsgBox "Drill & PL Transfer is Complete"
    End If
End Sub

Function IsFileOpen(fName As String) As Boolean
    Dim ff As Integer, errNum As Integer
    If Dir(fName, 0) = "" Then
        IsFileOpen = False: Exit Function
    End If
    On Error Resume Next
    ff = FreeFile
    Open fName For Input Lock Read As #ff
    Close ff
    errNum = Err
    On Error GoTo 0
    IsFileOpen = (errNum <> 0)
End Function

Thank you jindon, this is awesome. I've been testing on my C: drive. The report transfers the files, but gives an error when a copy of the file being saved is already open. It says "Run-time Error '70': Permission denied". In debug it stops at "FileCopy e(0), e(1)".

Sub DRILL_XFER()
'
' DRILL PL TRANSFER MACRO
' TRANSFER DRILLS AND PLS DAILY DURING CLOSE
'
    Dim e, msg As String
    For Each e In Array(Array("C:\Test_A\29DVS - 2018-12-31.xls", "C:\Test_B\29DVS - 12-2018.xls"), _
    Array("C:\Test_C\US WEST REGIONCOMP.xls", "C:\Test_D\US WEST REGIONCOMP - 2018-12-31.xls"))

        If Dir(e(0)) <> "" Then
            If Not IsFileOpen(CStr(e(0))) Then
                FileCopy e(0), e(1)
            Else
                msg = msg & vbLf & e(0) & " is in use"
            End If
        Else
            msg = msg & vbLf & e(0) & " is not found"
        End If
    Next
    If Len(msg) Then
        Sheets.Add.Cells(1).Resize(UBound(Split(msg, vbLf)) + 1).Value = _
        Application.Transpose(Split(msg, vbLf))
    Else

MsgBox "Drill & PL Transfer is Complete"
    End If

End Sub

Function IsFileOpen(fName As String) As Boolean
    Dim ff As Integer, errNum As Integer
    If Dir(fName, 0) = "" Then
        IsFileOpen = False: Exit Function
    End If
    On Error Resume Next
    ff = FreeFile
    Open fName For Input Lock Read As #ff
    Close ff
    errNum = Err
    On Error GoTo 0
    IsFileOpen = (errNum <> 0)
End Function

Not sure why, but see what happens.

Sub DRILL_XFER()
'
' DRILL PL TRANSFER MACRO
' TRANSFER DRILLS AND PLS DAILY DURING CLOSE
'
    Dim e, msg As String, fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each e In Array(Array("C:\Test_A\29DVS - 2018-12-31.xls", "C:\Test_B\29DVS - 12-2018.xls"), _
            Array("C:\Test_C\US WEST REGIONCOMP.xls", "C:\Test_D\US WEST REGIONCOMP - 2018-12-31.xls"))
        If fso.FileExists(e(0)) Then
            If Not IsFileOpen(CStr(e(0))) Then
                fso.CopyFile e(0), e(1), True
            Else
                msg = msg & vbLf & e(0) & " is in use"
            End If
        Else
            msg = msg & vbLf & e(0) & " is not found"
        End If
    Next
    If Len(msg) Then
        Sheets.Add.Cells(1).Resize(UBound(Split(msg, vbLf)) + 1).Value = _
        Application.Transpose(Split(msg, vbLf))
    Else

MsgBox "Drill & PL Transfer is Complete"
    End If

End Sub

Function IsFileOpen(fName As String) As Boolean
    Dim ff As Integer, errNum As Integer
    On Error Resume Next
    ff = FreeFile
    Open fName For Input Lock Read As #ff
    Close ff
    errNum = Err
    On Error GoTo 0
    IsFileOpen = (errNum <> 0)
End Function

Hello jindon. It seems to give the same error and stop at about the same place.

fso.CopyFile e(0), e(1), True

What are the both value for e(0) and e(1) when you get into a debug mode?

You can see when you hover over the cursor (not click) the variable.