Combine xls Files

[mgpco]

Hi there ,

My question is do u know any sort of program that can combine 100 xls Files (all has only one sheet ) to only one huge file in just one sheet !

and yes all the sheets having same no of columns and rows of data !!


Regards
H.B

 

[malkaff]

Hi,
I'm trying to merge a lot of excel files into one. I tried using this code but... nothing happens. I mean... I run it and then , what should I do?.
Could you please explain this a bit more ? For noobs like me .
Thx.

 

[vntuyen]

Dear all,
How can we put these excel files into one excel file but in seperate sheets?
Thank you!

 

[ravishankar]

HI
vntuyen: the macro in message #2 in the following link
http://www.mrexcel.com/forum/showthread.php?t=388644
collates sheet1 from all files into one master file
Ravi

 

[kevinjames]

Hi Guys,

This site is very useful.The code mentioned in this thread is almost matching my requirement. Can someone tell me how to move only selective records from each excel sheet like for eg. Value in B column = 'S&P'.

Regards,
Kevin

 

[itsmejan24]

Hi. I tried the code but I got a blank worksheets after. Please help. Thank you.

 

[nonneuron]

I tried Gnaga's code, and it worked! However, for my data, I need to merge the files such that each file should be copied and pasted to the next blank column (not Row). I've modified Gnaga's code to do this (see below), but it didn't work. Any help would be greatly appreciate:

Sub MergeSheets()

Dim SrcBook As Workbook

Dim fso As Object, f As Object, ff As Object, f1 As Object



Application.ScreenUpdating = False

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.Getfolder("C:\Temp\")

Set ff = f.Files



For Each f1 In ff

Set SrcBook = Workbooks.Open(f1)

Range("A1:IV" & Range("A65536").End(xlToLeft).Column).Copy

ThisWorkbook.Worksheets(1).Activate

Range("IV1").End(xlToLeft).Offset(0, 1).PasteSpecial

Application.CutCopyMode = False

SrcBook.Close

Next

End Sub

You could have used your earlier posting itself.

The following macro will do the job of copying all your data from indvidual workbook and paste into a single sheet.

No error trap is provided.

Copy your all individual workbooks into a single directory "C:\Temp\"
Open a new workbook. Right click the leftmost excel ison and click viewcode then paste this code and run.

Keep backup of your orignal data.


Sub MergeSheets()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:\Temp\")
Set ff = f.Files

For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub

GNaga

 

[volodos1986]

Dear Gnaga!!
This macro is really fantastic!! Thank you so much.
If it is possible, I would ask you one more thing.
I need to copy ton of excel files and your Macro is fantastic. Nevertheless, for each sheet I need to copy the name of each excel file for all the rows of any specific copied file:
For instance, considering two excel files: FILE1.xls and FILE2. xls

FILE1
a 1
b 4
c 5

FILE2
z 7
e 6
r 2

MERGE

FILE1 a 1
FILE1 b 4
FILE1 c 5
FILE2 z 7
FILE2 e 6
FILE2 r 2


Thank you so much four your help!!
All the best,
volodos

You could have used your earlier posting itself.

The following macro will do the job of copying all your data from indvidual workbook and paste into a single sheet.

No error trap is provided.

Copy your all individual workbooks into a single directory "C:\Temp\"
Open a new workbook. Right click the leftmost excel ison and click viewcode then paste this code and run.

Keep backup of your orignal data.


Sub MergeSheets()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:\Temp\")
Set ff = f.Files

For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub</pre>

GNaga

 

[excelbee2013]

Hi,

You can use this ready-made macro to merge all your files into one. It has simple options to choose multiple files and a short-cut will merge all data into one consolidated sheet.

thanks