excel newb needs to merge a bunch of files

[Robert Blackwell]

I don't use excel ever so it's pretty foreign to me.

I have a few dozen files that have contact information. I get them sent to
me each day or week from an ad agency that reports user interest for my
company. The fields in each file are exactly the same in all of the files.


I did a search in the help for "merge" and found the Compare and Merge
Workbooks suggestion, yet when I try it, it is grayed out in the tools menu,
regardless of me having one, none or all of the files open.

Any suggestions?

 

[Doug Kanter]

What format are the files sent to you? Excel, or other? If other.....?

 

[Bernie Deitrick]

Robert,

If all your files are in one folder, try the macro below. Assumes that
your data starts in cell A1 and is contiguous, with no blank rows or
columns, and that column A is completely filled.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

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("A66536").End(xlUp)(2)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated
file.xls")
End If
End With

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


End Sub

 

[Robert Blackwell]

They are all excel files. I'll see if I can try the macro

 

[Robert Blackwell]

I couldn't find out where to paste that code

 

[Ron de Bruin]

Check out this also Robert
http://www.rondebruin.nl/copy3.htm

A macro and a function you must copy in a normal module

Alt-F11
Insert - Module from the Menubar
Copy the code in there
Alt-Q to go back to Excel

In Excel you can do Alt-F8 and choose the macro in the list and run it

 

[Robert Blackwell]

I don't I get debug errors on both scrips you all suggested.

Why doesn't the compare and merge function work or show up for me? That
seems to be the simplest route considering each file has the same structure.

 

[Robert Blackwell]

Well, I was able to get the sheets into one excel file but it'd be nice to
merge all into just one sheet.

 

[Bernie Deitrick]

Robert,

The code
Range("A1").CurrentRegion.Copy
Basebook.Worksheets(1).Range("A66536").End(xlUp)(2)

Should all be on one line, and everything will get copied onto 1
sheet.

HTH,
Bernie
MS Excel MVP

 

[Robert Blackwell]

Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated
file.xls ")"

There's an error here same thing if I try putting it all on one line and
also with removing the trailing "

 

[Bernie Deitrick]

Robert,

The version below should survive the message wrapping without errors.
Give it a try.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
..DisplayAlerts = False
..EnableEvents = False
..ScreenUpdating = False
End With

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("A66536").End(xlUp)(2)
myBook.Close
Next i
Basebook.SaveAs Application. _
GetSaveAsFilename("Consolidated file.xls ")
End If
End With

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

End Sub