Sub test()
Dim myDir$, fn$, i&, s$, LR&, wb As Workbook
Const wsName$ = "B2B", r$ = "A6:U6", wbName$ = "combined"
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then myDir = .SelectedItems(1) & "\"
End With
If myDir = "" Then Exit Sub
fn = Dir(myDir & wbName & ".xls*")
If fn = "" Then MsgBox "No workbook named " & wbName & " found", vbCritical: Exit Sub
Application.ScreenUpdating = False
Set wb = Workbooks.Open(myDir & fn)
wb.Sheets(wsName).[a1].CurrentRegion.Offset(1).ClearContents
For i = 1 To 12
fn = Dir(myDir & i & ".xls*")
If fn <> "" Then
LR = wb.Sheets(wsName).Evaluate("max(if(a1:t100<>"""",row(1:100)))") + 1
s = "'" & myDir & "[" & fn & "]" & wsName & "'!" & Split(r, ":")(0)
With wb.Sheets(wsName).Cells(LR, 1).Resize(Range(r).Rows.Count, Range(r).Columns.Count)
.Formula = "=if(" & s & "<>""""," & s & ","""")"
.Value = .Value
.Columns(.Columns.Count + 1) = fn
End With
End If
Next
wb.Close True
Application.ScreenUpdating = True
End Sub