This tutorial shows you how to combine or merge multiple excel workbooks into one in just 2 minutes. its lot easier to share or make operations on single file. so Just follow these steps.
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:
'Merges all files in a folder to a main file.
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
Set sourceWorkbook = ActiveWorkbook
'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
'Close the source workbook
- 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.