Вам нужно приложить ваше регулярное выражение в ^
(начало строки) и $
(конец строки):
^Red October$
Ниже код работал для меня (Изменить пример пути):
Public Sub DataToSummary()
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim LastRowMaster As Long
Dim DataRowsMaster As Long
Dim LastRowSource As Long
Dim FileNameSource As String
Dim i As Integer, intValueToFind As Integer
Path = "C:\Example\Path\"
Filename = Dir(Path & "*.xlsx")
Do While Len(Filename) > 0
Set wbk = Workbooks.Open(Path & Filename)
For i = 1 To 500
If Cells(i, 1).Value = intValueToFind Then
GoTo Skip
End If
Next i
LastRowSource = Cells(Rows.Count, 2).End(xlUp).Row
DataRowsSource = LastRowSource - 6
FileNameSource = Left(Filename, Len(Filename) - 5)
Workbooks(Filename).Sheets(1).Range("B7:M" & LastRowSource).Copy
Workbooks("Master.xlsb").Activate
LastRowMaster = Cells(Rows.Count, 6).End(xlUp).Row
ThisWorkbook.Sheets(1).Range("F" & LastRowMaster + 1).PasteSpecial xlPasteValues
ThisWorkbook.Sheets(1).Range("B" & LastRowMaster + 1 & ":B" & LastRowMaster + DataRowsSource).Value = FileNameSource
ThisWorkbook.Sheets(1).Range("C1:E1").Copy
ThisWorkbook.Sheets(1).Range("C" & LastRowMaster + 1 & ":E" & LastRowMaster + DataRowsSource).PasteSpecial xlPasteFormulas
Skip:
wbk.Close True
Filename = Dir
Loop
End Sub