merge excel sheets

[ydeng]

Hi all

I NEED SOME TIPS AS TO HOW TO MERGE MORE THAN 1000 EXCEL
BOOKS INTO ONE MASTER FILE. THESE INDIVIDUAL EXCEL FILES
HAVE SAME FORMAT, BUT DATA NEED TO BE MERGED INTO ONE
MASTER FILE. ANY BATCH SCRIPT IS APPRECIATED.

THANKS,
Y

[Bernie Deitrick]

Y,

The macro below will take all worksheets in all workbooks in a folder
(and below in sub-folders if needed) and put then onto one sheet. See
the caveats in the macro, which you can modify as needed.

HTH,
Bernie

Sub Consolidate()
' Will consolidate Mulitple Sheets
' from Multiple Files onto one sheet
' Never tested with files that would
' give more than one sheets as end result
' Assumes that all data starts in cell A1 and
' is contiguous, with no blanks in column A

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False 'Change to true if needed
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
For Each mySheet In myBook.Worksheets
mySheet.Activate
Range("A1").CurrentRegion.Copy _

Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
Next mySheet
myBook.Close
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub