how to combine several files, all with same columns, into one shee

Let say there is 3 excel files. Each file has one sheet and the same columns,
e.g. column A: product name
column B: description
Column C: Price

How can I combine all the 3 sheets in different files into one sheet of a
new file?
I don't want to copy and paste, because in reality, I have more than 100
files like this.

Thank you!

Landa,

Assumptions are: 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.Copy _
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


"Landa" <[email protected]> wrote in message
news:[email protected]...
> Let say there is 3 excel files. Each file has one sheet and the same columns,
> e.g. column A: product name
> column B: description
> Column C: Price
>
> How can I combine all the 3 sheets in different files into one sheet of a
> new file?
> I don't want to copy and paste, because in reality, I have more than 100
> files like this.
>
> Thank you!

Thank you very much, Bernie!

"Bernie Deitrick" wrote:

> Landa,
>
> Assumptions are: 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.Copy _
> 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
>
>
> "Landa" <[email protected]> wrote in message
> news:[email protected]...
> > Let say there is 3 excel files. Each file has one sheet and the same columns,
> > e.g. column A: product name
> > column B: description
> > Column C: Price
> >
> > How can I combine all the 3 sheets in different files into one sheet of a
> > new file?
> > I don't want to copy and paste, because in reality, I have more than 100
> > files like this.
> >
> > Thank you!
>
>
>

> Thank you very much, Bernie!
>


You're quite welcome....

Bernie
MS Excel MVP