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

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!