How to Merge Multiple Excel Sheets in One View

By:

Whenever you work on MS Excel, you have to deal with a large number of excel sheets at the same time. This is very time-consuming and confusing to maintain and operate on a such number of sheets simultaneously. To overcome this issue, you can merge all the excel spreadsheets into a single one to make your excel processes simpler. There are many ways to combine multiple excel sheets in one view and some of them are move & copy sheets, power query and vba codes.

Move & Copy Sheets

This is the simplest way to merge bulk excel spreadsheets. The full procedure of this method is described below

  • First, open all the excel sheets (in different workbooks), then select one workbook and copy all the sheets.
  • Select all the sheets together then right-click and select the “Move or Copy” option.
  • Click on the To book dropdown button and select the workbook where you want to move these sheets.
  • Under the Before Sheet menu, click on the (move to end) option. You can also tick on Create a Copy checkbox if you want to keep a separate copy of the excel sheets.
  • Now, press Ok, and all the excel sheets are now moved to a different workbook.
  • Repeat all the above steps for all the remaining workbooks and at last, all the excel files are merged into a separate workbook.

But there is a drawback to this method, your old workbooks, and newly merged workbook are not synchronized, which means if you make changes in the excel sheets that are moved to another workbook, then it will not reflect in the newly merged excel book.


Power Query

Power Query is another best method to merge multiple excel sheets into one single sheet.

Conditions to use the Power Query method Accurately

The excel sheets you are going to merge should have the same header values.

Case 1: Merge multiple excel spreadsheets of a single workbook together

    Step 1:

    select workbook

    Step 2:

    transform data

    Step 3:

    append queries

    Step 4:

    close and load

    Step 5:

    merged spreadsheet
  • Create and open a new workbook. Go to the Data tab, click “Get Data” → “From File” → “From Workbook” and then select the workbook that contains all the spreadsheets.
  • Check the select multiple items option, tick all the sheets that you want to merge, and click on the “Transform Data” option.
  • You will see all your excel sheets under the Queries section. Click on the “Append Queries” option to combine all the excel sheets into a single table, and then click Ok.
  • Select the “Close & Load” option present on the top-left side of the window and save this newly created merged spreadsheet.
  • Now, the merged excel sheet is successfully loaded into your newly created workbook as shown below

Case 2: Merge multiple excel workbooks (containing a single spreadsheet)

  • Firstly, you will need to ensure that each sheet should have the same name in all the workbooks and then, place all the workbooks in a particular folder and save it to a particular location.
  • Create and open a new Excel Workbook, go to the Data tab, click “Get Data” → “From File” → “From Folder” and select the folder containing all the workbooks.
  • Click on the “Combine” button and select the Combine & Transform Data option.
  • Select the sheet of any workbook and see the preview of the table present in it on the right side of the window. Now press Ok.
  • Click on the “Close & Load” option to save this merged spreadsheet to your newly created workbook.

Case 3: Merge multiple excel workbooks (containing multiple spreadsheets)

  • Firstly, open all of your workbooks together that contain multiple spreadsheets.
  • Now, move all the spreadsheets into a separate workbook. For this, use the Move & Copy Sheets method as discussed above.
  • Once you moved all your spreadsheets into a single workbook, then repeat all the steps mentioned in Case 1 to merge all of them into a single excel sheet.

Combine Multiple Excel Sheets with VBA

This method is supported by all excel versions. VBA stands for Visual Basic for Applications. It is a programming language that is mainly developed for MS Excel. In this method, you will use pre-created macros to modify your excel operations.

  • Firstly, put all your workbooks in a single folder, then create and open a new Excel spreadsheet.
  • To open the VBA window, press the shortcut key Alt+F11.
  • Go to the “Insert” menu and then click the “Module” option.
  • Copy and Paste any of the following VBA codes according to your requirements to merge your excel sheets present in different workbooks.
    Code 1: Merge Multiple Excel Files into an Active Workbook as Individual Sheets
    Sub GetSheets()
    Path = "C:\Users\abc\Desktop\New folder\"
    Filename = Dir(Path & "*.xls")
     Do While Filename <> ""
     Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
     Next Sheet
    Workbooks(Filename).Close
     Filename = Dir()
     Loop
    End Sub
                            

    Code 2: Merge Multiple Excel Files into a New Workbook as Individual Sheets
    Sub MergeMultipleFiles()
    On Error GoTo eh
    'declare variables to hold the objects required
        Dim wbDestination As Workbook
        Dim wbSource As Workbook
        Dim wsSource As Worksheet
        Dim wb As Workbook
        Dim sh As Worksheet
        Dim strSheetName As String
        Dim strDestName As String
    'turn off the screen updating to speed things up
        Application.ScreenUpdating = False
    'first create new destination workbook
        Set wbDestination = Workbooks.Add
    'get the name of the new workbook so you exclude it from the loop below
        strDestName = wbDestination.Name
    'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook
        For Each wb In Application.Workbooks
            If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
                Set wbSource = wb
                For Each sh In wbSource.Worksheets
                sh.Copy After:=Workbooks(strDestName).Sheets(1)
                Next sh
            End If
        Next wb
    'now close all the open files except the new file and the Personal macro workbook.
        For Each wb In Application.Workbooks
            If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
                wb.Close False
            End If
        Next wb
    'remove sheet one from the destination workbook
        Application.DisplayAlerts = False
        Sheets("Sheet1").Delete
        Application.DisplayAlerts = True
    'clean up the objects to release the memory
        Set wbDestination = Nothing
        Set wbSource = Nothing
        Set wsSource = Nothing
        Set wb = Nothing
    'turn on the screen updating when complete
        Application.ScreenUpdating = False
    Exit Sub
    eh:
        MsgBox Err.Description
    End Sub
                            

    Code 3: Merge Multiple Excel Files into One Sheet in a New Workbook
    Sub MergeMultipleSheetsToNew()
    On Error GoTo eh
    'declare variables to hold the objects required
        Dim wbDestination As Workbook
        Dim wbSource As Workbook
        Dim wsDestination As Worksheet
        Dim wb As Workbook
        Dim sh As Worksheet
        Dim strSheetName As String
        Dim strDestName As String
        Dim iRws As Integer
        Dim iCols As Integer
        Dim totRws As Integer
        Dim strEndRng As String
        Dim rngSource As Range
    'turn off the screen updating to speed things up
        Application.ScreenUpdating = False
    'first create new destination workbook
        Set wbDestination = Workbooks.Add
    'get the name of the new workbook so you exclude it from the loop below
        strDestName = wbDestination.Name
    'now loop through each of the workbooks open to get the data
        For Each wb In Application.Workbooks
            If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
                Set wbSource = wb
                For Each sh In wbSource.Worksheets
    'get the number of rows and columns in the sheet
                sh.Activate
                ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
                iRws = ActiveCell.Row
                iCols = ActiveCell.Column
    'set the range of the last cell in the sheet
                strEndRng = sh.Cells(iRws, iCols).Address
    'set the source range to copy
                Set rngSource = sh.Range("A1:" & strEndRng)
    'find the last row in the destination sheet
                wbDestination.Activate
                Set wsDestination = ActiveSheet
                wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
                totRws = ActiveCell.Row
    'check if there are enough rows to paste the data
                If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
                    MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
                    GoTo eh
                End If
    'add a row to paste on the next row down
                If totRws <> 1 Then totRws = totRws + 1
                rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
            Next sh
        End If
        Next wb
    'now close all the open files except the one you want
        For Each wb In Application.Workbooks
            If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
                wb.Close False
            End If
        Next wb
    'clean up the objects to release the memory
        Set wbDestination = Nothing
        Set wbSource = Nothing
        Set wsDestination = Nothing
        Set rngSource = Nothing
        Set wb = Nothing
    'turn on the screen updating when complete
        Application.ScreenUpdating = False
    Exit Sub
    eh:
    MsgBox Err.Description
    End Sub
                            
    Code 4: Merge Multiple Excel Files into One Sheet in an Active Workbook
    Sub MergeMultipleSheetsToActive()
    On Error GoTo eh
    'declare variables to hold the objects required
        Dim wbDestination As Workbook
        Dim wbSource As Workbook
        Dim wsDestination As Worksheet
        Dim wb As Workbook
        Dim sh As Worksheet
        Dim strSheetName As String
        Dim strDestName As String
        Dim iRws As Integer
        Dim iCols As Integer
        Dim totRws As Integer
        Dim rngEnd As String
        Dim rngSource As Range
    'set the active workbook object for the destination book
        Set wbDestination = ActiveWorkbook
    'get the name of the active file
        strDestName = wbDestination.Name
    'turn off the screen updating to speed things up
        Application.ScreenUpdating = False
    'first create new destination worksheet in your Active workbook
        Application.DisplayAlerts = False
    'resume next error in case sheet doesn't exist
        On Error Resume Next
        ActiveWorkbook.Sheets("Consolidation").Delete
    'reset error trap to go to the error trap at the end
        On Error GoTo eh
        Application.DisplayAlerts = True
    'add a new sheet to the workbook
        With ActiveWorkbook
            Set wsDestination = .Sheets.Add(After:=.Sheets(.Sheets.Count))
            wsDestination.Name = "Consolidation"
        End With
        'now loop through each of the workbooks open to get the data
        For Each wb In Application.Workbooks
            If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
                Set wbSource = wb
                    For Each sh In wbSource.Worksheets
        'get the number of rows in the sheet
                    sh.Activate
                    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
                    iRws = ActiveCell.Row
                    iCols = ActiveCell.Column
                    rngEnd = sh.Cells(iRws, iCols).Address
                    Set rngSource = sh.Range("A1:" & rngEnd)
        'find the last row in the destination sheet
                    wbDestination.Activate
                    Set wsDestination = ActiveSheet
                    wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
                    totRws = ActiveCell.Row
        'check if there are enough rows to paste the data
                    If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
                        MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
                        GoTo eh
                    End If
        'add a row to paste on the next row down if you are not in row 1
                    If totRws <> 1 Then totRws = totRws + 1
                    rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
                Next sh
                End If
        Next wb
        'now close all the open files except the one you want
        For Each wb In Application.Workbooks
            If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
                wb.Close False
            End If
        Next wb
        'clean up the objects to release the memory
        Set wbDestination = Nothing
        Set wbSource = Nothing
        Set wsDestination = Nothing
        Set rngSource = Nothing
        Set wb = Nothing
        'turn on the screen updating when complete
        Application.ScreenUpdating = False
        Exit Sub
        eh:
        MsgBox Err.Description
        End Sub
                            
  • Save your current workbook with the xlsm file extension to enable the macros.
  • After that, press the F5 key on the keyboard to run the macro.
  • Now, you will realize that all the workbooks present in the folder are merged into a single excel workbook.