Combine Excel worksheets of the same name into one spreadsheet

This article describes 3 possible ways to merge worksheets with the same name from multiple workbooks into a single Excel sheet and explains how to choose the solution best suited for your needs.

Heavy users of Microsoft Excel would agree that combining data from several files into a single worksheet is one of the most frequent tasks that nearly everyone performs once in a while. This global task seems to have an infinite number of variations and still there are a few most common scenarios. Today we are going to look into one of them, namely, how to combine worksheets with the same names from separate Excel workbooks into one sheet.

Say, every month your company receives dozens of sales reports from its regional branches or subsidiaries. All of them have the same worksheet names, e.g. January, February, March, and so on. What you need is to consolidate data from all identically named spreadsheets into one. In this article we will tell you how to do this quickly and efficiently.

The task is to merge data from the same name worksheets into one sheet

The image above shows what we are looking for. And now let's see how you can achieve this:

Solution 1: Copy and paste data manually
Tedious and error-prone way. Might work if you have very few worksheets to combine and a lot of patience.

Solution 2: Merge worksheets using a VBA macro
Complicated way, for advanced users. Requires solid knowledge of VBA and needs to be adjusted for each particular task.

Solution 3: Use the Copy Sheets tool
Accurate and faultless results in the shortest time. Works with all Excel versions and data types.

Copy and paste data across multiple workbooks manually

Copy and paste
If you have plenty of time and nothing special to do, this solution may work for you. Create a new summary Excel workbook into which you will copy data from all the worksheets. Then insert the needed number of sheets and name them accordingly - January, February, etc. Now all you need is time and patience... and attention... and a bit of luck.

Open your first file, copy all entries from the January worksheet, switch to the Summary workbook and inset the copied data into the January worksheet there. After that open the second file, go to the January sheet, copy and paste and repeat the same circle with all other spreadsheets. Hmm, wait... are you sure you copied data from Colorado subsidiary's report? And how many times? Well, you'd better double-check :)

Summing up

Advantages: a free solution that does not require any learning curve, it can help to fill a few hours of your free time.

Drawbacks: you may easily forget to copy data from some workbook or insert it twice, or copy from a wrong worksheet, or insert into the wrong sheet. All in all, this is a very time-consuming and error-prone way, in worst case it'd be several hours of your time down the drain.

Merge worksheets using a VBA macro

Writing a macro to copy worksheets of the same name into one sheet
There exist tons of macros that can automate your Excel tasks, in theory. In practice, you need to be pretty comfortable with VBA to write such a macro yourself, or have at least some basic knowledge to be able to sift hundreds of existing samples to find the one best suited for your needs.

If you do have such knowledge, go ahead! The link displays Google search results for "VBA macro to copy worksheets of same name into one".

Summing up

Advantages: the majority of macros are free, you might even succeed in finding some that will work for you.

Drawbacks: you need to learn VBA to be able to write or find a macro for your particular purposes and update it every time the merging conditions change. Mind you, the paths to the source data should be specified directly in the code, a simple mouse click won't work. So, it will definitely take you a few hours to get some valuable pieces of working code and adjust them for your task and data type.

Use the Copy Sheets tool

And now I will show you how to achieve the same goal with no pain in less than 3 minutes! We are going to use the Copy Sheets tool included with our Ultimate Suite for Excel. Among other useful features, it provides a smart 3-step wizard to quickly combine several worksheets with the same name into a single sheet. Now you know why I said "in less than 3 min" - one minute per each step :) With little practice you won't actually need so much time for that, each step will take you just a few seconds. Okay, let's see this merging tool in action.

3 easy steps to merge multiple sheets with the same name into one

First off, you need to install the Ultimate Suite, luckily a fully-functional trial version is available, so you can download it right now. Once you have the add-in installed, open Excel and you will see the Ablebits Data tab with the Copy Sheets icon on your Excel ribbon.
The Copy Sheets tool for Excel

As you remember, our task is to merge several sales reports from the company's subsidiaries into a single Excel workbook by copying data from all identically named worksheets:
Combining multiple Excel worksheets with the same name into one

Step 1. Choose how to combine the worksheets

You start by choosing how you wish to merge the spreadsheets. As you can see in the screenshot below, the tool provides four options to choose from. Since we are concerned with combining the same name worksheets, we select the 2nd one - Copy data from the selected sheets with the same name to one sheet.
Specify how to combine the worksheets

Step 2: Select the worksheets you want to combine

If you have the workbooks already open, the Copy Sheets add-in will pick them and display for you. If you want to add some more worksheets, click the Add Files button. By default, the used range (All data) is selected for each spreadsheet, as you see in the screenshot:
Select the worksheets you need to combine

If you want to change the range, click the Collapse Dialog button Collapse Dialog button next to the worksheet.

Step 3. Choose how to paste data to the summary workbook

On this step, you specify exactly how you want to have the data pasted into the resulting sheet. In particular, you decide whether you want to preserve the original data's format, paste all data or values only, or maybe link the copied data to the source entries. When done, click Copy.

For our example, we keep the defaults, which work fine in most cases:
Specify how to paste data from the same name sheets to the destination workbook

Allow the tool a little while for processing and enjoy the results :)
Multiple Excel worksheets are successfully merged into one.

As you see, 25 worksheets with the same names were successfully combined in under 3 minutes! Just think how much time you would spend to do this daunting job manually.

Summing up:

Advantages: impeccable results in the shortest time, plus a variety of options to cope with different merging tasks.

Drawbacks: it is not free (but is definitely worth its money :)

So, don't hesitate to download the evaluation version and try it on your own data. The tool works with all Excel versions, from 2007 to 2019 32-bit and 64-bit, on all Windows versions and environments.

If you have any questions, just post a quick comment here, I am keen to know your opinions. Thank you for reading and see you!

3 responses to "Combine Excel worksheets of the same name into one spreadsheet"

  1. sharath kumar says:

    hi sir,thank you for valuable information about excel.
    i have a problem in excel.i am using 2003 excel.there is workbook called "bif.xls".the path of bif.xls is "c:\myfiles".
    in that workbook over 68 macros are there .i want to place all macros into a single bas file,then it is easy to backup all my macros by just backup that bas file.i want a macro to place all macros into a single bas file.please help me in this issue and please email me macro to do the task.
    waiting for your valuable reply,
    thanking you sir,
    sharath kumar,
    email: sharath_kumar1997@yahoo.com,
    india.

  2. ajay kumar says:

    hi sir i am ajay thakur and can you can help me?
    sir please convert vba excel pla help me fast

  3. ASADULLAH says:

    Any way its Awesome

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard