Download the RDBMerge Add-in. This would be the best way to accomplish it.
Download and put it somewhere you can find it and enable it in excel. I recommend using the default add-in location at C:\Documents and Settings\User\Application Data\Microsoft\AddIns\
Click on File, click on
Options, click the Add-ins tab. In the Manage drop-down, choose Excel
Add-ins, and click Go. Use “Browse” to go to the add-in and then click
on OK. Verify RDBMerge is checked in the add-in list and then click
OK.
Then in excel you'll find a button on the ribbon for it, click it and follow the directions.
Or with 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