<div>
Though the obvious choice is to use Power Query if you are using Excel 2010 or later, here is a VBA code that will do the trick. 
 
In the attached file, you will find a button on Sheet1 called Combine Files, you may click this button to run the code. The code will prompt you to select the folder where all your excel files are saved. 

<pre><code class="code-10 nolinks" id="code-20-41735442-1">Sub CombineFilesIntoOneFile()
Dim dwb As Workbook, swb As Workbook
Dim dws As Worksheet, sws As Worksheet
Dim slr As Long, slc As Long
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim SeletedFolder As String

Application.ScreenUpdating = False
Set dwb = ThisWorkbook
Set dws = dwb.Sheets(&quot;Sheet1&quot;)
dws.Cells.Clear
Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot;)

With Application.FileDialog(msoFileDialogFolderPicker)
 .Title = &quot;Select A Folder!&quot;
 .ButtonName = &quot;Confirm&quot;
 If .Show = -1 Then
 SelectedFolder = .SelectedItems(1)
 Set folder = fso.GetFolder(SelectedFolder)
 Else
 MsgBox &quot;You didn't select a folder.&quot;, vbExclamation, &quot;Folder Not Selected!&quot;
 Exit Sub
 End If
End With
For Each file In folder.Files
 If InStr(file.Name, dwb.Name) = 0 And Left(fso.GetExtensionName(file), 2) = &quot;xl&quot; Then
 Workbooks.Open file
 Set swb = ActiveWorkbook
 Set sws = swb.Sheets(1)
 slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
 slc = sws.Cells(1, Columns.Count).End(xlToLeft).Column
 If dws.Range(&quot;A1&quot;).Value = &quot;&quot; Then
 sws.Rows(1).Copy dws.Range(&quot;A1&quot;)
 End If
 sws.Range(&quot;A2&quot;, sws.Cells(slr, slc)).Copy dws.Range(&quot;A&quot; &amp; Rows.Count).End(3)(2)
 swb.Close True
 End If
 Set swb = Nothing
Next file
dws.Columns.AutoFit
Application.ScreenUpdating = True
MsgBox &quot;Data from all the Files have been copied into one Sheet successfully copied to all the Workbooks.&quot;, vbInformation, &quot;Done!&quot;
End Sub
</code></pre>
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1109194/CombineFilesIntoOneFile.xlsm" target="_blank" class="file-inline" title="CombineFilesIntoOneFile.xlsm (20 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>CombineFilesIntoOneFile.xlsm</a>
</div>

CombineFilesIntoOneFile.xlsm

<div>
Here's some code that I have used for years. 
 

<pre><code class="code-10 nolinks" id="code-20-41735446-1">Option Explicit


'---------------------------------------------------------------------------------------
' Module : Data
' Author : Roy Cox (royUK)
' Website : for more examples and Excel Consulting
' Date : 19/11/2011
' Purpose : Combine data from several workbooks
' Disclaimer: Disclaimer; This code is offered as is with no guarantees. You may use it in your
' projects but please leave this header intact.
 
'---------------------------------------------------------------------------------------



Sub CombineData()
 Dim oWbk As Workbook
 Dim rRng As Range
 Dim rToCopy As Range
 Dim rNextCl As Range
 Dim lCount As Long
 Dim bHeaders As Boolean
 Dim sFil As String
 Dim sPath As String

 With Application
 .ScreenUpdating = False
 .DisplayAlerts = False
 .EnableEvents = False
 ' On Error GoTo exithandler
 ' assumes workbooks are in a sub folder named &quot;Data&quot;
 sPath = ThisWorkbook.Path &amp; Application.PathSeparator &amp; &quot;Data&quot;
 ChDir sPath
 sFil = Dir(&quot;*.xl**&quot;) 'file type
 Do While sFil &lt;&gt; &quot;&quot; 'will start LOOP until all files in folder sPath have been looped through

 With ThisWorkbook.Worksheets(1)
 Set rRng = .Range(&quot;A1&quot;).CurrentRegion
 If rRng.Cells.Count = 0 Then
 'no data in master sheet
 bHeaders = False
 Else: bHeaders = True
 End If

 Set oWbk = Workbooks.Open(sPath &amp; Application.PathSeparator &amp; sFil) 'opens the file
 'A1 must be within the data, if not amend the Range below
 Set rToCopy = oWbk.ActiveSheet.Range(&quot;A1&quot;).CurrentRegion
 If Not bHeaders Then
 Set rNextCl = .Cells(1, 1)
 bHeaders = True
 Else: Set rNextCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
 'headers exist so don't copy
 Set rToCopy = rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
 rToCopy.Columns.Count)
 End If
 rToCopy.Copy rNextCl
 End With
 oWbk.Close False 'close source workbook
 sFil = Dir
 Loop ' End of LOOP
 'sort to remove empty rows
 Set rRng = ThisWorkbook.Worksheets(1).UsedRange
 rRng.Sort Key1:=.Range(&quot;A2&quot;), Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortNormal
exithandler:
 .ScreenUpdating = True
 .DisplayAlerts = True
 .EnableEvents = True
 End With
End Sub
</code></pre>
</div>

<div>
@Neeraj, I can't open the file. Says it is not a valid format or extension? &nbsp;I am using Excel 2007... 
@Roy, can you sort of idiot proof some instruction on how to use that code? &nbsp;I really am not much of a tech guy...but I can follow 2nd grader instructions.
</div>

<div>
Which version of Excel are you using? I can &nbsp;provide you with a suitable workbook with the code in if you let me know which version. 
 
The code that I have posted will work in any version of Excel. 
 
It requires the source workbooks to be in a sub folder called Data within the folder that contains the workbook with the code in.
</div>

<div>
Thanks Roy, I am using Excel 2007. &nbsp;If you could idiot proof it, that would be awesome!
</div>

<div>
Do you have an example of the data and the resulting merge?
</div>

<div>
I attached a file, for example. 
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1109215/Merged-really-was-just-copy-paste.xls" target="_blank" class="file-inline" title="Merged-really-was-just-copy-paste.xls (17 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Merged-really-was-just-copy-paste.xls</a> 
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1109216/Merged-really-was-just-copy-paste.xls" target="_blank" class="file-inline" title="Merged-really-was-just-copy-paste.xls (17 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Merged-really-was-just-copy-paste.xls</a>
</div>

Merged-really-was-just-copy-paste.xls

<div>
<blockquote>
@Neeraj, I can't open the file. Says it is not a valid format or extension? &nbsp;I am using Excel 2007
</blockquote>
 
That's a temporary bug here. I also have the same issue at my end and report this bug to tech team and hopefully it will resolved soon. 
 
To deal with this, download and SAVE this workbook on your system and then open it. 
 
The workbook attached belongs to the Excel 2013, 
 
Another option is, you can just copy the code into a blank workbook's standard module and run the code from there.
</div>

CombineFilesIntoOneFile.xls

<div>
@Roy....Is the file I uploaded downloading as an Excel file? I uploaded the Excel file, but when I tried to download it just now, it keeps coming in as a Word doc. &nbsp;Were you able to download it as an Excel file?
</div>

<div>
@wfcrr 
 
I tried downloading one of your sample workbook and it was opened in Excel application. So no issue at my end. That seems to be another bug. Lol
</div>

<div>
@Neeraj, I was able to open the file you uploaded, but when I hit the button &quot;Combine Files&quot; and navigate to the folder that has the excel files, nothing in the folder if viewable. &nbsp;All the excel files in the folder are excel 2007 files, but, none of them appear in the folder when I navigate using the &quot;Combine Files&quot; button...what should I do?
</div>

<div>
While navigating to the source folder, you don't need to open or double click the folder instead you just select the source folder and click on Confirm to proceed. 
Remember it's a folder picker dialog box not the file picker.
</div>

<div>
I'm just downloading your files. They are opening OK for me. Why are you using.xls if your version is Excel 2007?
</div>

<div>
I need to know if you are using .xls or .xlsm.
</div>

<div>
@Roy, I don't know why I'm using .xls. &nbsp;What should I be doing? I just saved the file... 
Also, I have to leave the office, can you upload the file you want me to use? I'll plan to look at it either tomorrow, or for sure on Monday morning. Will you be around on Monday?
</div>

<div>
I have added the code to workbook based on your downloads. 
 
Basically the code will import data from a sub folder &nbsp;named Data which stores the data files. This folde should be in the main folder that houses the master workbook. 
 
To run the code there is a new section in the right of the Data Tab of the Ribbon named Merge Files with a button to run the code. 
 
As you won't be testing immediately I'm going to add some features and attach the example soon.
</div>

Data-files.png

Merge-Files.xlsm

Merge-Files-Tab.png

<div>
<div class="content wysiwyg-content">
I have approximately 50 different excel files I need to turn into one file. Is there a way to do that? &nbsp;I am not sure if I should be saying merge, or combine, or convert...what I need to do is have all the data that is in 50 separate excel files somehow now be in one excel file. &nbsp;If I can't find a method for doing that, I will simply copy/paste from each file into one very large file. &nbsp;However, I am thinking you guys probably have a formula or method for doing that?
</div>
</div>

I have approximately 50 different excel files I need to turn into one file. Is there a way to do that?  I am not sure if I should be saying merge, or combine, or convert...what I need to do is have all the data that is in 50 separate excel files somehow now be in one excel file.  If I can't find a method for doing that, I will simply copy/paste from each file into one very large file.  However, I am thinking you guys probably have a formula or method for doing that?

How to combine or merge 50 excel spreadsheets

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.