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!

Thanks for the response. The blank rows will occur below the data in each of
the files to be combined.

Mike

"Ron de Bruin" wrote:

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!

I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message ...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message ...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

Read good

If the header is in row 1 the enter A2 in StartCell

--

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


"Heliocracy" wrote in message ...
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message ...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

Okay got it. However, it's still pulling "blank" rows from the files to be
combined. The rows aren't really blank, because there are drop-down menus in
2 columns and conditional formulae in 3 (the formulae make the cell "" when
FALSE). When the drop-downs are set to blank, and the formulae are all
false, the row is blank, but still gets pulled into the combined file.

Using your RDB_Filter_Value macro can make the "blank" lines disappear, but
that macro cannot be used when the files to be combined are Protected, and
this is necessary. Any way around these problems?

Thanks again,
Mike

"Ron de Bruin" wrote:

Read good

If the header is in row 1 the enter A2 in StartCell

--

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


"Heliocracy" wrote in message ...
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message ...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

I've solved all the problems except two:

1. After using the RDB_Filter_Values, my Excel Properties "Calculation"
option is set to "manual".

2. The Autofilters on the files which were combined become
non-functional--they have to be removed and re-installed to make them work
again. I see there is code to prevent that, but it doesn't appear to be
working.

Thanks,
Mike

"Heliocracy" wrote:

Okay got it. However, it's still pulling "blank" rows from the files to be
combined. The rows aren't really blank, because there are drop-down menus in
2 columns and conditional formulae in 3 (the formulae make the cell "" when
FALSE). When the drop-downs are set to blank, and the formulae are all
false, the row is blank, but still gets pulled into the combined file.

Using your RDB_Filter_Value macro can make the "blank" lines disappear, but
that macro cannot be used when the files to be combined are Protected, and
this is necessary. Any way around these problems?

Thanks again,
Mike

"Ron de Bruin" wrote:

Read good

If the header is in row 1 the enter A2 in StartCell

--

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


"Heliocracy" wrote in message ...
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message ...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

You can add code to unprotect the worksheet and protect the worksheet in the Get_Filter macro if you want
Is the worksheet password the same in all files ?


--

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


"Heliocracy" wrote in message ...
Okay got it. However, it's still pulling "blank" rows from the files to be
combined. The rows aren't really blank, because there are drop-down menus in
2 columns and conditional formulae in 3 (the formulae make the cell "" when
FALSE). When the drop-downs are set to blank, and the formulae are all
false, the row is blank, but still gets pulled into the combined file.

Using your RDB_Filter_Value macro can make the "blank" lines disappear, but
that macro cannot be used when the files to be combined are Protected, and
this is necessary. Any way around these problems?

Thanks again,
Mike

"Ron de Bruin" wrote:

Read good

If the header is in row 1 the enter A2 in StartCell

--

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


"Heliocracy" wrote in message ...
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message
...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

If the code crash you have this problem

--

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


"Heliocracy" wrote in message ...
I've solved all the problems except two:

1. After using the RDB_Filter_Values, my Excel Properties "Calculation"
option is set to "manual".

2. The Autofilters on the files which were combined become
non-functional--they have to be removed and re-installed to make them work
again. I see there is code to prevent that, but it doesn't appear to be
working.

Thanks,
Mike

"Heliocracy" wrote:

Okay got it. However, it's still pulling "blank" rows from the files to be
combined. The rows aren't really blank, because there are drop-down menus in
2 columns and conditional formulae in 3 (the formulae make the cell "" when
FALSE). When the drop-downs are set to blank, and the formulae are all
false, the row is blank, but still gets pulled into the combined file.

Using your RDB_Filter_Value macro can make the "blank" lines disappear, but
that macro cannot be used when the files to be combined are Protected, and
this is necessary. Any way around these problems?

Thanks again,
Mike

"Ron de Bruin" wrote:

Read good

If the header is in row 1 the enter A2 in StartCell

--

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


"Heliocracy" wrote in message ...
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message
...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

Hi Ron,

If the blank rows are within the data is it possible to skip those rows?




"Ron de Bruin" wrote:

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!

Hi

Rows with a blank cell in a column or a whole blank row ?

After you merge the data into a new sheet you can loop through all rows or filter os sort.
For deleting rows see
http://www.rondebruin.nl/delete.htm



--

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


"Stephid707" wrote in message ...
Hi Ron,

If the blank rows are within the data is it possible to skip those rows?




"Ron de Bruin" wrote:

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!

Hi Ron,
The column may have a blank row in it. When I use the macro it stops at the
blank row and moves to the next file. Sometime the blank row is in the middle
of the file. Is there a way for the macro to skip the blank row and continue
copying the rest of the column?

"Ron de Bruin" wrote:

Hi

Rows with a blank cell in a column or a whole blank row ?

After you merge the data into a new sheet you can loop through all rows or filter os sort.
For deleting rows see
http://www.rondebruin.nl/delete.htm



--

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


"Stephid707" wrote in message ...
Hi Ron,

If the blank rows are within the data is it possible to skip those rows?




"Ron de Bruin" wrote:

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!

Bernie's code works for me but I have two questions:
It works in a new worksheet (as directed) when I tried to move it to
Personal.xls, I can't get it to work. It runs with a blank result. Is this
pilot error or is there a fix?

Can the code be modified to combine .csv files?
.FileType = msoFileTypeExcelWorkbooks
=msoFileTypeCoomaSeparatedValues. N/G

Thanks for your help. A great resource.
wal50

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

It paste to

ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 )

ThisWorkbook = your personal file now

For csv this is also a option
http://www.rondebruin.nl/csv.htm

Or use my add-in
http://www.rondebruin.nl/merge.htm



--

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


"wal50" wrote in message ...
Bernie's code works for me but I have two questions:
It works in a new worksheet (as directed) when I tried to move it to
Personal.xls, I can't get it to work. It runs with a blank result. Is this
pilot error or is there a fix?

Can the code be modified to combine .csv files?
.FileType = msoFileTypeExcelWorkbooks
=msoFileTypeCoomaSeparatedValues. N/G

Thanks for your help. A great resource.
wal50

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

Worked like a charm. Thanks again to you and all the other wizards who make
the lives of mere mortals that much easier.
wal50

"Ron de Bruin" wrote:

It paste to

ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 )

ThisWorkbook = your personal file now

For csv this is also a option
http://www.rondebruin.nl/csv.htm

Or use my add-in
http://www.rondebruin.nl/merge.htm



--

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


"wal50" wrote in message ...
Bernie's code works for me but I have two questions:
It works in a new worksheet (as directed) when I tried to move it to
Personal.xls, I can't get it to work. It runs with a blank result. Is this
pilot error or is there a fix?

Can the code be modified to combine .csv files?
.FileType = msoFileTypeExcelWorkbooks
=msoFileTypeCoomaSeparatedValues. N/G

Thanks for your help. A great resource.
wal50

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

Great Ron,
Entering A2 in startCell will remove any header. But what if I need to keep
the header to be in the first row of the new sheet and not repated from every
file I combine?


"Ron de Bruin" wrote:

Read good

If the header is in row 1 the enter A2 in StartCell

--

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


"Heliocracy" wrote in message ...
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined
file to contain every header row from each of the files to be combined. I
will live with that if I have to, though. Thanks for your help!

Mike

"Ron de Bruin" wrote:

I think you use the FSO download from
http://www.rondebruin.nl/fso.htm

Do you use a fixed range ?

If you enter StartCell then it will copy till the last cell with data from that cell
Read the info on the webpage


In the "Get_Data" macro in the Get_Data_Macro" module change

'Add a new workbook with one sheet named "Combine Sheet"
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Name = "Combine Sheet"

To

'Use the Activesheet to paste the data
Set BaseWks = Activesheet



--

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


"Heliocracy" wrote in message ...
I'm using the macro in the Excel file downloaded from your site. Thanks.

"Ron de Bruin" wrote:

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!

In the process of helping clients manage their spreadsheets I have developed
a web application that loads, organizes and reports upon a portfolio of
spreadsheets.
This application is not yet being offered as a "product" or "service" but I
invite you to check it out at http://www.spreadsheetmanagement.com/
I may be able to help you extract the information you need.


"Landa" wrote:
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 Ron

I have run the RDB Merge Ad-in, to merge 95 Excel files. There is just 1
problem, it merged the files and data, but the fill colours of the cells are
now "no fill" and that is the most important part of my merge! How do I do
the merge and make sure that the cell fill colours are also duplicated?

Thanks
Babs

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

Dear Bernie,
Great work.
Please help me that how to save this macro, Do I have to paste again and
again..
--
With gratitude,

Faisal Ijaz


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