M-TechRidge Blog

How to merge or Combine multiple Excel files into one in two minutes




Change Language:

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 combine multiple sheet(Worksheet) into one sheet

How to merge or Combine multiple Excel files into one in two minutes

  1. First of all copy or move all excel files in one folder as shown in image.



  2. 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.

  3. 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
       
    End Sub

  4. Click on Run button to run this code:

  5. 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.

  6. 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.



Watch Full Video Tutorial:


comment

ADMIN
24-08-2020

Try This: https://mtechridge.com/single.php?title=How-to-combine-multiple-sheet-Worksheet-into-one-sheet
comment

RYAN
23-08-2020

How do I append though?


Leave a Comment:

Validation code:


Can't read the image? click here to refresh.