Te envío una macro que se adapta a tus necesidades
Sub Macro3()
' Written by Juan Manuel Cosceri (25-01-2018)
Set TOOL = Workbooks(ActiveWorkbook.Name)
Set ConsolidatedSH = TOOL.Sheets("Sheet2") 'Reemplazar SHEET2 por el nombre de la hoja donde ira el Output
ConsolidatedLR = ConsolidatedSH.Cells(Rows.Count, 1).End(xlUp).Row 'Setea el Last row de esta hoja para no pisar la info que ya esta
' Input Selection by user
TrackInputs = Application.GetOpenFilename(, , "Select Files", , True) 'Abre explorador para seleccionar archivos (Acepta MULTISELECT)
On Error GoTo NoInput
For I = 1 To UBound(TrackInputs)
Workbooks.Open (TrackInputs(I)) 'Abre el input
On Error GoTo 0
Set NewWB = Workbooks(ActiveWorkbook.Name)
Application.StatusBar = "Importing Data - File " & I '(loading bar)
NewWB.Activate
Set shTrack = NewWB.Worksheets(1) 'Selecciona la Hoja 1 del input Workbook
'Define la fila y columna iniciales
InputHR = 1
InputHC = 1
'Define Last Row y last column para copiar toda la data
shTrack.Activate
lastRow = shTrack.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastColumn = shTrack.Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
TOOL.Activate
ConsolidatedLR = ConsolidatedSH.Cells(Rows.Count, 1).End(xlUp).Row
'Copia la info y la pega en el source de data
shTrack.Activate
shTrack.Range(Cells(InputHR, InputHC), Cells(lastRow, LastColumn)).Copy
TOOL.Activate
ConsolidatedSH.Range("A" & ConsolidatedLR + 1).PasteSpecial xlPasteValues
'Cierra el Input
Application.DisplayAlerts = False
NewWB.Close
Application.DisplayAlerts = True
Next I 'Pasa al siguiente input si hubiese
Application.StatusBar = "Done!" '(loading bar)
NoInput:
'
End Sub
Por favor recuerda valorar la respuesta!
Slds
Juan