How can I merge different excel files into one?

[Kaloyanski]

Greetings!



My question is how can I merge different excel files/workbooks into 1 file? Is there any formula so that I do not do it manually? Can you please help me?

Edit:I am using Excel 2010 Thanks
Thanks in advance.
Kaloyan

[Catalin.B]

You can try this macro: (first select all worksheets to be merged with ctrl+sheet tab)

Sub MergeSheets()

'  Appends data from all the selected worksheets onto the end of the
'  active worksheet.

   Const NHR = 1     'Number of header rows to not copy from each MWS
   
   Dim MWS        As Worksheet   'Worksheet to be merged (appended)
   Dim AWS        As Worksheet   'Worksheet to which the data are transferred
   Dim FAR        As Long        'First available row on AWS
   Dim LR         As Long        'Last row on the MWS sheets
   
   Set AWS = ActiveSheet
   
   For Each MWS In ActiveWindow.SelectedSheets
      If Not MWS Is AWS Then
         FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
         LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
         MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
      End If
   Next MWS

End Sub

[Nirik]

Dear Catalin.B
The code provided above is possible for merging different sheets of one file into one, but if someone want to merge different files date (having same headers) into one file can the code will be helpfull.

[Catalin.B]

for merging different workbooks, you can find an appropriate code here:
(for merging all workbooks, or just a range from selected workbooks, and so on)
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx