Option Explicit
' >>>>> Put the initial path where the files to be processed are stored here. _
End with backslash
Const sInitialPath = "C:\MyPath\"
Sub GetData()
Dim wbIn As Workbook, wbOut As Workbook
Dim rIn As Range, rOut As Range
Dim wsIn As Worksheet, wsOut As Worksheet
Dim diaFolder As FileDialog
Dim lCount As Long
Set wbOut = ThisWorkbook
' Assuming masterWB has only one sheet
Set wsOut = wbOut.Sheets(1)
' get file name for file to process
MsgBox "Select all the files you want to process by using the Ctrl key and the mouse. "
' Open the file dialog to get the files
Set diaFolder = Application.FileDialog(msoFileDialogFilePicker)
With diaFolder
.AllowMultiSelect = True
.InitialView = msoFileDialogViewList
.InitialFileName = sInitialPath
lCount = .Show
End With
If lCount = -1 Then
' for each selected file
For lCount = 1 To diaFolder.SelectedItems.Count
Set wbIn = Workbooks.Open(diaFolder.SelectedItems(lCount))
'loop through all the sheets in the opened book
For Each wsIn In wbIn.Sheets
'set output range on the Mastersheet to last row
Set rOut = wsOut.Cells(wsOut.Rows.Count, 1).End(xlUp).Offset(1, 0)
'now copy the values accross to the Mastersheet
With wsIn.Range("A1:C60")
rOut.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
Next wsIn
'close WB
wbIn.Close savechanges:=False
Next lCount
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
'Cleanup
Set wbIn = Nothing
Set wbOut = Nothing
Set rIn = Nothing
Set rOut = Nothing
Set wsIn = Nothing
Set wsOut = Nothing
Set diaFolder = Nothing
End Sub