dim strOpenFileName (5 to 7) as String, bytFile as byte

Function FileExist(strFilename As String) As Boolean
    FileExist = (Dir(strFilename) <> "")
End Function


Sub CheckExistFile
For bytFile = 5 to 7
    strOpenFilename(bytFIle) = "D:\MyProject\"&Sh_Consolidate.Range("F" & bytFile).Value

    if fileexist(strOpenfilename(bytfile)) = false then
      msgbox "System will terminated process because file name : " &"'" & srfilename(bytfile) &"'" &" was not found!",vbokonly+vbinformation
        end
    end if
    next bytFile
else
    ConsolidationData
end if

end sub

Sub ConsolidationData
    Dim strSoureFilename as String
    Application.ScreenUpdating = False
    For bytFile = 5 To 7
        strSourceFilename = "D:\MyProject\&Sh_Consolidate.Range("F" & bytFile).Value
        Workbooks.Open Filename:= strSourceFilename
      
        Range("B7", Range("B1048576").End(xlUp)).Copy
        'Workbooks(1) คือไฟล์ปลายทาง
        workbooks(1).activate
        Sh_Consolidate.Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        workbooks(2).activate
        Range("D7", Range("D1048576").End(xlUp)).Copy
        workbooks(1).activate
        Sh_Consolidate.Range("C1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        'Workbooks(2) คือ File แหล่งข้อมูล
        workbooks(2).close false
    Next bytFile
    Msgbox "Consolidation process is completed!",vbokonly
    Application.ScreenUpdating = True
End Sub