How to merge worksheets from multiple Excel workbooks into one

If you’re reading this then you’ve probably got some Excel workbooks with worksheets in each and you’d like to get all the worksheets into a single workbook.

There are several options you can use to merge Excel files for free:

Each of these options are covered in the following sections, click the item in the list above to jump to that section.

Excel Document StackNot exactly what you need?
Have a look at The Ultimate Guide on how to Merge Excel files for some other ways to merge Excel files.

Copy & Paste the worksheets into the master spreadsheet

This is the easiest of the options but also can be time consuming if you have a lot of spreadsheets. It involves opening each spreadsheet and copying the worksheets into the master spreadsheet.

[ordered_list style=”decimal”]
  1. Choose one of the spreadsheets you want to merge worksheets from to be the master spreadsheet and open it (Pro tip: pick the one with the most worksheets!)
  2. Open the next spreadsheet to copy the worksheets from
  3. Right click the first worksheet and select ‘Move or Copy…’:
    Excel Move or copy Worksheet
  4. In the Move or Copy dialog box that is shown, ensure that the master spreadsheet is selected in the ‘To book:’ dropdown and select the sheet you would like to insert the copied sheet before:
    Excel Move or copy Worksheet dialog
  5. Click the ‘OK’ button then check that the worksheet was copied into the master spreadsheet:
    Address Worksheet moved
  6. Repeat steps 2 to 5 for each of the spreadsheets and worksheets you would like to copy into the master workbook
[ordered_list]

Use a VBA (Visual Basic for Applications) macro to merge the worksheets into the master spreadsheet

This is a little more technical than the copy & paste method, this method works well when you have lots of worksheets or lots of Excel files to copy worksheets from. If you follow the steps below, you should have your files merged in no time.

I’m going to describe 2 methods of doing the merge or worksheets here:

For either of these approaches, I’ve provided links to spreadsheets with the code in them at the end of the instructions.

Merge all worksheets from Excel files in a specific folder

With this approach, you put all of your Excel files to copy worksheets from into a single folder (with nothing else in it). Using the VBA code provided below, all of the worksheets from all of the Excel files in this folder will be added to the master workbook.

[ordered_list style=”decimal”]
  1. Create a folder for the Excel files to be merged
  2. Copy & paste all the files into this new folder
  3. Create a new master Excel workbook in a location that is NOT the folder created in step 1. This workbook will contain all the copied worksheets once we’re done.
  4. In this new workbook, click on the Developer ribbon tab
    Excel Developer Ribbon Tab
    If the Developer ribbon tab is not present, follow these steps to get it
  5. Click the Macros button in the Developer tab (The Macros button on a Mac)
    Excel Macros Button
  6. In the dialog box which opens, type the name CopyExcelWorksheets and click the ‘Create’ button
    CopyExcelWorksheets Macro
  7. The Visual Basic Editor will open:
    CopyExcelWorksheets Macro VBA
  8. Copy & paste (or re-type by hand if you prefer!) the code below between Sub CopyExcelWorksheets()  and End Sub  (if you’d like to know more about what this code does, see how the code works):
  9. Look for the line shown below and replace <<Full path to your Excel files folder>> with the full path to the folder you created in step 1 (e.g. C:\Users\Martin\Documents\CopyExcelWorksheets)
  10. We can now run this macro to merge the Excel files. There are 2 ways to do this:
    1. Click the Run toolbar button in the Visual Basic Editor
      CopyExcelWorksheets Run Macro In VBA
    2. In the Developer tab in the Excel ribbon, click Macros, select CopyExcelWorksheets and click the Run button
      CopyExcelWorksheets Run Macro
    3. If there are any errors reported, check that the VBA code is correct and try again
  11. If you’d like to save this spreadsheet with the macro you just created in it, you will need to save the file as a Macro-Enabled Workbook (an xldm file). Follow these steps to do this:
    1. Click File -> Save as…
    2. Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the Format list
      CopyExcelWorksheets Save as
    3. Click the Save button
[/ordered_list]

If you’d like a copy of the spreadsheet which already has this functionality built in, you can download it here.

Use a form to choose the Excel files to merge worksheets from

This approach is similar to the previous method but here we’ll create a form where you can choose which Excel files to copy worksheets from rather than put them all into a single folder. This makes it easy to re-use if you ever need copy worksheets again without creating specific folders.

[ordered_list style=”decimal”]
  1. Create a new Excel workbook
  2. In this new workbook, click on the Developer ribbon tab
    Excel Developer Ribbon Tab
    If the Developer ribbon tab is not present, follow these steps to get it
  3. Click the Visual Basic button in the Developer tab (The Editor button on a Mac)
    Excel Visual Basic Button
  4. The Visual Basic Editor will launch
    Excel Visual Basic Editor
  5. To the left and top of the Visual Basic Editor, there is a window labeled ‘Project’. Right click on the node within that window called ‘VBAProject’ and select Insert->User Form
    Excel VBA Insert User Form
  6. A new window will open with a gray square, this is the blank form. There will also be a toolbox window containing a set of controls which you can add to the form
    Excel VBA New User Form
  7. We need a list box and 3 buttons laid out as shown in the image below:
  8. Excel VBA User Form Layout
  9. For the VBA code to work correctly, you will need to change the names of the controls on the form and also update the button labels while we’re at it
    1. Click on the List Box you added
    2. On the left of the Visual Basic Editor, there is a window labeled ‘Properties’
    3. In this window, change the value of the (Name) property to FilesListBox
      Excel Set FilesListBox Name
    4. Click on each button in turn and the checkbox then change the (Name) property and the Caption property so that they match the image shown below:
      Excel VBA User Form Layout With Names
      The (Name) and Caption properties should match those in the table below:

      Caption(Name)
      Add Excel FilesAddFilesButton
      CancelCancelButton
      Copy SheetsCopySheetsButton

      Excel Add Files Properties

    5. Whilst in the properties for the Copy Sheets button, set the Enabled property to false
      Excel Merge Button Disabled
  10. We can also give the form a name. Click on the form title ‘UserForm1’ then, in the Properties window, change the (Name) property to CopyExcelWorksheetsForm
  11. Once the controls have been named correctly, right click on the UserForm1 node in the Project window and select View Code
    Excel User Form View Code
  12. Copy & paste (or re-type by hand if you prefer!) the code below into the window that opens (if you’d like to know more about what this code does, see how the code works):

  13. To show our new form and perform the merge, we will create a macro. In Excel, click the Macros button in the Developer tab (The Macros button on a Mac)
    Excel Macros Button
  14. In the dialog box which opens, type the name CopyExcelWorksheets and click the ‘Create’ button
    CopyExcelWorksheets Macro
  15. The Visual Basic Editor will open:
  16. Copy & paste (or re-type by hand if you prefer!) the code below. This will simply show the form which we have just created

  17. We can now run this macro to merge the Excel files. There are 2 ways to do this:
    1. Click the Run toolbar button in the Visual Basic Editor
      Excel VBA Run Button
    2. In the Developer tab in the Excel ribbon, click Macros, select CopyExcelWorksheets and click the Run button
      CopyExcelWorksheets Run Macro
  18. If there are any errors reported, check that the VBA code is correct and try again
  19. If you’d like to save this spreadsheet with the macro you just created in it, you will need to save the file as a Macro-Enabled Workbook (an xldm file). Follow these steps to do this:
    1. Click File -> Save as…
    2. Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the Format list
      CopyExcelWorksheets With Form-Save as
    3. Click the Save button
[/ordered_list]

If you’d like a copy of the spreadsheet which already has this functionality built in, you can download it here.