VBA double-loop search and copy

Hi here is what you need:

Option Explicit
Sub doubleLoop()
Dim c As Range, c2 As Range
Dim strFileName As String, Path As String
Dim strIncrFileName As String, strIncrWksName As String
Dim wks As Worksheet, wksTmp As Worksheet
Dim wkbTmp As Workbook

For Each c In ThisWorkbook.Worksheets("YahBoh").Range("B1:D1") 'Loops through the files names increments

    strIncrFileName = "Yadayada" + CStr(c) + "*" '* will work for any format
    Path = "C:\Users\stagiaire\Desktop\Forums questions\" 'PLUG YOUR PATH HERE
    strFileName = Dir(Path & strIncrFileName) 'Gets the file name
    
    If strFileName <> vbNullString Then 'If a file has been found
    
        For Each c2 In ThisWorkbook.Worksheets("YahBoh").Range("A2:A4") 'Loops through the worksheets names increments
            
            Set wkbTmp = Workbooks.Open(Path & strFileName, , True) 'Opens and sets the temporary workbook read-only
            
            strIncrWksName = c2
            
            Set wksTmp = wkbTmp.Worksheets(strIncrWksName)
            
            ThisWorkbook.Worksheets("YahBoh").Cells(c2.Row, c.Column) = wksTmp.Range("$D$1")
                            
        Next
        
        wkbTmp.Close False 'Closes the temporary workbook without saving
        
    End If
    
Next

End Sub

It works with the examples sheets you provided, you just have to replace the names, path and ranges accordingly to your needs.