Загрузите надстройку RDBMerge. Это был бы лучший способ сделать это.
Скачайте и положите его куда-нибудь, вы можете найти его и включить в Excel. Я рекомендую использовать расположение надстройки по умолчанию в C:\Documents and Settings\User\Application Data\Microsoft\AddIns\
Нажмите «Файл», нажмите «Параметры», перейдите на вкладку «Надстройки». В раскрывающемся меню «Управление» выберите «Надстройки Excel» и нажмите «Перейти». Используйте «Обзор», чтобы перейти к надстройке, а затем нажмите «ОК». Убедитесь, что RDBMerge отмечен в списке надстроек, и нажмите кнопку ОК.
Затем в Excel вы найдете кнопку на ленте для нее, нажмите ее и следуйте инструкциям.
Или с VBA
Sub CopyBooks()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim destinationWorkbook As Workbook
Set destinationWorkbook = ThisWorkbook
Dim sourceWorkbook As Workbook
Dim sourceWorksheet As Worksheet
Const path As String = "C:\path\to\"
Dim file As Variant
Dim currentSheets As Long
currentSheets = destinationWorkbook.Sheets.Count
file = Dir(path & "*.xl*")
While file <> ""
Set sourceWorkbook = Workbooks.Open(path & file)
For Each sourceWorksheet In sourceWorkbook.Worksheets
sourceWorksheet.Copy after:=destinationWorkbook.Worksheets(currentSheets)
currentSheets = currentSheets + 1
Next
sourceWorkbook.Close savechanges:=False
file = Dir
Wend
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub