merging exxcel files

[mark s]

Is it possible to merge an n number of excel files into one excel file. So
we can manipulate all the data in just one single file.

Thank you,

m_1...@hotmail.com

[Bernie Deitrick]

Mark,

Here is a macro that will merge all sheets of all files in a single folder
into one workbook. If you want all the data on one, worksheet, then run the
second macro, which will copy the block of cells that includes A1 onto a
single sheet (the assumption being that the data is in a database starting in
A1 and with no blank rows or columns).

HTH,
Bernie

Sub ConsolidateAllSheetsIntoOneSpreadSheet()
Dim mySht As Worksheet
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
OpenIt = MsgBox("File: " & .FoundFiles(i) & Chr(13) & _
"Do you want to include this?", vbYesNo)
If OpenIt = vbYes Then
Workbooks.Open .FoundFiles(i)
For Each mySht In ActiveWorkbook.Worksheets
mySht.Name = ActiveWorkbook.Name & "- " & mySht.Name
mySht.Select (False)
Next mySht
ActiveWindow.SelectedSheets.Move After:=ThisWorkbook.Sheets(1)

End If
Next i
Else: MsgBox "There were no files found."
End If

End With

End Sub

Sub ConsolidateFirstSheetIntoOneSheet()

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Range("A1").CurrentRegion.Copy
Basebook.Worksheets(1).Range("a1").End(xlDown).Offset(1, 0)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls")

End If
End With

End Sub

[mark s]

Thank you for the quick response I have not had the time to try the macro
but it looks interesting

Again Thank you
mark s
"Bernie Deitrick" <dei...@consumer.org> wrote in message
news:3AAE1EBC...@consumer.org...