Something like this?
Not Tested
Option Explicit
Sub Check_File_and_Open()
frmInput.Show
wsFolderPath.Range("I1").Value = dtReportDate
wsFolderPath.Range("J1").Value = strMTOName
Path = wsFolderPath.Range("B2").Value
'============ Check File and Open Debtor workbook========================
Set wbk_Debtor = OpenTheFile(Path, strMTOName & " Debtor Report")
If wbk_Debtor Is Nothing Then GoTo MyEnd
'===============Check File and Open Creditor===============
Set wbk_Creditor = OpenTheFile(Path, strMTOName & " Creditor")
If wbk_Creditor Is Nothing Then GoTo MyEnd
'====================Check Sales1 Workbook and Open==============================
Set wbk_Sales1 = OpenTheFile(Path, strMTOName & " Sales Register 001")
If wbk_Sales1 Is Nothing Then GoTo MyEnd
'====================Check File Sales2 Workbook==============================
Set wbk_Sales2 = OpenTheFile(Path, strMTOName & " Sales Register 002")
If wbk_Sales2 Is Nothing Then GoTo MyEnd
MyEnd:
'stuff
End Sub
Private Function OpenTheFile(P As String, KW As String) As Workbook
Dim strFile As String
strFile = Dir(P & "\*" & KW & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set OpenTheFile = Workbooks.Open(P & Application.PathSeparator & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & KW & " not found in a Folder " & P, vbCritical, "Select Correct Date or Check Files in A Folder"
Set OpenTheFile = Nothing
End If
End Function
or
Option Explicit
Sub Check_File_and_Open2()
frmInput.Show
wsFolderPath.Range("I1").Value = dtReportDate
wsFolderPath.Range("J1").Value = strMTOName
Path = wsFolderPath.Range("B2").Value
If Not OpenTheFile2(wbk_Debtor, Path, strMTOName & " Debtor Report") Then GoTo MyEnd
If Not OpenTheFile2(wbk_Creditor, Path, strMTOName & " Debtor Report") Then GoTo MyEnd
If Not OpenTheFile2(wbk_Sales1, Path, strMTOName & " Debtor Report") Then GoTo MyEnd
If Not OpenTheFile2(wbk_Sales2, Path, strMTOName & " Debtor Report") Then GoTo MyEnd
MyEnd:
'stuff
End Sub
Private Function OpenTheFile2(ByRef WB As Workbook, P As String, KW As String) As Boolean
Dim strFile As String
strFile = Dir(P & "\*" & KW & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set WB = Workbooks.Open(P & Application.PathSeparator & strFile, False, True)
Application.DisplayAlerts = True
OpenTheFile2 = True
Else
MsgBox "Workbook :- " & KW & " not found in a Folder " & P, vbCritical, "Select Correct Date or Check Files in A Folder"
OpenTheFile2 = False
End If
End Function