Como copiar un rango de muchos archivos de excel en un solo libro

Esta es la solución: Abre un libro nuevo en blanco y pega esta macro en un módulo, después ejecútala y todo listo.

Sub juntar_archivos()
mio = ActiveWorkbook.Name
ChDir "c:\main\" 
archi = Dir("*.xl*")
Do While archi <> ""
fila = Workbooks(mio).Sheets(1).Range("a65000").End(xlUp).Row + 1
Workbooks.Open archi
Sheets("hoja2").Range("a2:bv16").Copy Destination:=Workbooks(mio).Sheets(1).Cells(fila, 1)
ActiveWorkbook.Close False
archi = Dir()
Loop
End Sub

recuerda finalizar y puntuar