Sub MergingMultipleFiles_IntoMasterworkbook()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sPath As String, sFile As String, sName As String
Dim lr1 As Long, lr2 As Long
'user data section
Set sh1 = ThisWorkbook.Sheets("Master") 'Master workbook sheet name
sPath = "C:\trabajo\files\" 'desktop folder name
sName = "Raw Data" 'common sheet name
sh1.Cells.Clear
sh1.Range("ZZ1").Value = "original file name"
sFile = Dir(sPath & "*.xls*")
Do While sFile <> ""
If HasSheet(sPath, sFile, sName) Then
Set sh2 = Workbooks.Open(sPath & sFile).Sheets(sName)
lr1 = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
lr2 = sh2.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
sh2.Rows("1:" & lr2).Copy sh1.Range("A" & lr1)
sh1.Range("ZZ" & lr1).Resize(lr2).Value = sFile
sh2.Parent.Close False
End If
sFile = Dir()
Loop
End Sub
Function HasSheet(fPath As String, fName As String, sheetName As String)
Dim f As String
f = "'" & fPath & "[" & fName & "]" & sheetName & "'!R1C1"
HasSheet = Not IsError(Application.ExecuteExcel4Macro(f))
End Function