Business Barcodes.net
Download Barcode Software
Home » Resources » How to merge multiple excel sheets in one view

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

  • 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. select workbook
  • Check the select multiple items option shown in the left panel, tick all the sheets that you want to merge, and click on the “Transform Data” option. transform data
  • 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. append queries
  • Now, select the “Close & Load” option present on the top-left side of the window and save this newly created merged excel spreadsheet. close load
  • Now, the merged excel sheet is successfully loaded into your newly created workbook as shown below merged spreadsheet

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.

Note: If there are some entries that are same in your multiple excel sheets and you want to merge all those sheets without contaning any duplicate value, then first merge all the excel spreadsheets into single one by using any one of the above methods. Second, in your newly created merged sheet, select all the entries, click “Data Tab” → “Remove Duplicates” and then press Ok. Now, this will automatically delete all the duplicate entries present in your merged excel sheet.