How to merge or Combine multiple Excel files into one in two minutes
First of all copy or move all excel files in one folder as shown in image.
Now Open Merged.xlsx (in this file all other excel file will be merged). After open this file go to VBA editor by Alt+F11. This is Visual basic for application windows. Now in Insert menu click on module. A editor will show up on screen.
Now Copy and paste this code:
Sub mergeFiles() 'Merges all files in a folder to a main file.
'Define variables: Dim numberOfFilesChosen, i As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbook Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker)
'Allow the user to select multiple workbooks tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
'Loop through all selected workbooks For i = 1 To tempFileDialog.SelectedItems.Count
'Open each workbook Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
'Copy each worksheet to the end of the main workbook For Each tempWorkSheet In sourceWorkbook.Worksheets tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count) Next tempWorkSheet
'Close the source workbook sourceWorkbook.Close Next i
Click on Run button to run this code:
Browse popup window will appear after executing this code. Go to the location where all files you put to be merged and select all these files then click on ok.
All files will open and close automatically. It will take time depend of no of files. after few minutes when it stops close VBA windows and see all files have been merged.