Copied from another post...
[Subject: how to combine several files, all with same columns, into one
shee 7/11/2006 5:35 AM PST

By: Bernie Deitrick In: microsoft.public.excel.misc ]

Assumptions a data starts in cell A1, the table is contiguous, is on the
first sheet of the
workbook, and all 100 files are in one folder. Also, the total in all files
is less than 65536 rows
of data.

Copy the macro below into a codemodule of a new workbook, change the path
where indicated, and run
it. When it is done, save the workbook.

HTH,
Bernie
MS Excel MVP


Sub Consolidate()
Dim myBook As Workbook
Dim myCalc As XlCalculation
Dim myShtName As String

With Application
.EnableEvents = False
.DisplayAlerts = False
myCalc = .Calculation
.Calculation = xlCalculationManual
End With

On Error Resume Next
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Files to combine"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 )
myBook.Close False
Next i
Else: MsgBox "There were no files found."
End If
End With
With Application
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With

End Sub


"James" wrote:

I have something on the range of 50 or so different workbooks. There are
multiple worksheets within these workbooks, but I am only concerned with
Sheet1. Is there a way to compile all these different Sheet1's into one
single work book and pasting them as values. All the workbooks of intrest
reside in the same folder so there are no subfolders.

Thanks for any help.