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

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!