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 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


"Landa" wrote in message
...
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 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


"Landa" wrote in message
...
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

The solution posted by Bernie works great for me when combining the 1st sheet
of multiple workbooks. But, how can it be modified to combine the 2nd sheet
of several excel workbooks?

Thanks in advance for any assistance.

"Bernie Deitrick" wrote:

Thank you very much, Bernie!



You're quite welcome....

Bernie
MS Excel MVP

Hello-

I have used this code (trying to do my homework first!) and changed the
path- it looks as though when it runs it opens all of the files and closes
them but does not copy any data into the book1 file that I have opened - what
am I doing wrong here?

"Bernie Deitrick" wrote:

Landa,

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


"Landa" wrote in message
...
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!

Look at this page for another way (see also the add-in)
http://www.rondebruin.nl/copy3.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Katie" wrote in message ...
Hello-

I have used this code (trying to do my homework first!) and changed the
path- it looks as though when it runs it opens all of the files and closes
them but does not copy any data into the book1 file that I have opened - what
am I doing wrong here?

"Bernie Deitrick" wrote:

Landa,

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


"Landa" wrote in message
...
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!

Hi Bernie,

I tried the code and it opens every file in my folder succesfully. The
problem I am having is that each new file overrides the contents of the prior
file. At the end of the macro, I am only able to see the headers...which are
the headers for 3 files...
Is there a property on my worksheet that I need to set?

Here is how I did it:

1. I open the macro editor and (in excel , book1)
2. inserted a MODULE, copied your code, change the code and ran the code.

Here is how I chance your code:
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 = "S:\Lsshare\Bankruptcy\Closeouts\"
.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


"Bernie Deitrick" wrote:

Landa,

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


"Landa" wrote in message
...
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!

I'm using your macros as well, but each of the files I'm trying to combine
will have many blank rows on them...is there a way to modify your macros so
that:

1. The combined data can be placed on a worksheet of my choosing, that
already exists, starting on Row 2?
2. Blank rows are ignored when combining, so they don't end up in the
combined worksheet?

Great code BTW, and thanks in advance for your help!

Heliocracy

"Ron de Bruin" wrote:

Look at this page for another way (see also the add-in)
http://www.rondebruin.nl/copy3.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Katie" wrote in message ...
Hello-

I have used this code (trying to do my homework first!) and changed the
path- it looks as though when it runs it opens all of the files and closes
them but does not copy any data into the book1 file that I have opened - what
am I doing wrong here?

"Bernie Deitrick" wrote:

Landa,

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


"Landa" wrote in message
...
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!

Hi Heliocracy

1: Yes that is possible, which macro do you use ?

If you use one from the Dir page then chnage

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

To

Set BaseWks = Worksheets("Yoursheet")
rnum = 2


2: Are the blank rows in the data or below the data ?





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Heliocracy" wrote in message ...
I'm using your macros as well, but each of the files I'm trying to combine
will have many blank rows on them...is there a way to modify your macros so
that:

1. The combined data can be placed on a worksheet of my choosing, that
already exists, starting on Row 2?
2. Blank rows are ignored when combining, so they don't end up in the
combined worksheet?

Great code BTW, and thanks in advance for your help!

Heliocracy

"Ron de Bruin" wrote:

Look at this page for another way (see also the add-in)
http://www.rondebruin.nl/copy3.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Katie" wrote in message ...
Hello-

I have used this code (trying to do my homework first!) and changed the
path- it looks as though when it runs it opens all of the files and closes
them but does not copy any data into the book1 file that I have opened - what
am I doing wrong here?

"Bernie Deitrick" wrote:

Landa,

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


"Landa" wrote in message
...
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!