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

[Landa]

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

Landa,

Assumptions are: data starts in cell A1, the table is contiguous, is on the first sheet of the
workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows
of data.

Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run
it. When it is done, save the workbook.

HTH,
Bernie
MS Excel MVP


Sub Consolidate()
Dim myBook As Workbook
Dim myCalc As XlCalculation
Dim myShtName As String

With Application
.EnableEvents = False
.DisplayAlerts = False
myCalc = .Calculation
.Calculation = xlCalculationManual
End With

On Error Resume Next
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Files to combine"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets(1).Range("A1").CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2)
myBook.Close False
Next i
Else: MsgBox "There were no files found."
End If
End With
With Application
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With

End Sub

 

[Landa]

Thank you very much, Bernie!

Landa,

Assumptions are: data starts in cell A1, the table is contiguous, is on the first sheet of the
workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows
of data.

Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run
it. When it is done, save the workbook.

HTH,
Bernie
MS Excel MVP


Sub Consolidate()
Dim myBook As Workbook
Dim myCalc As XlCalculation
Dim myShtName As String

With Application
.EnableEvents = False
.DisplayAlerts = False
myCalc = .Calculation
.Calculation = xlCalculationManual
End With

On Error Resume Next
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Files to combine"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets(1).Range("A1").CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2)
myBook.Close False
Next i
Else: MsgBox "There were no files found."
End If
End With
With Application
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With

End Sub

 

[Bernie Deitrick]

Thank you very much, Bernie!

You're quite welcome....

Bernie
MS Excel MVP

 

[BenS]

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.

 

[Katie]

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?

 

[Ron de Bruin]

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

 

[LaRana]

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.Copy _
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2)
myBook.Close False
Next i
Else: MsgBox "There were no files found."
End If
End With
With Application
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With

End Sub

 

[Heliocracy]

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]

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 ?

 

[Heliocracy]

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

Mike

 

[Heliocracy]

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

 

[Ron de Bruin]

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

 

[Heliocracy]

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]

Read good

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

 

[Heliocracy]

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

 

[Heliocracy]

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

 

[Ron de Bruin]

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 ?

 

[Ron de Bruin]

If the code crash you have this problem

 

[Stephid707]

Hi Ron,

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