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 Consolidate Worksheets Wizard
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 Consolidate Worksheets Wizard

And now I will show you how to achieve the same goal with no pain in less than 5 minutes! We are going to use the Consolidate Worksheets add-in, which among other consolidation options provides a smart 5-step wizard to quickly combine several worksheets with the same name into a single spreadsheet. Now you know why I said "in less than 5 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 consolidation tool in action.

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

First off, we need to install the Consolidate Worksheets Wizard, luckily a 30-day trial version is available, so you can download it right now. Once you have the add-in installed, open Excel and you will see a new tab with the Consolidate Worksheets icon residing on the Excel ribbon.
The Consolidate Worksheets Wizard icon on the Excel ribbon

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. Select the worksheets you want to combine

You start by choosing the worksheets you want to merge. If you have the workbooks already open, the Consolidation Wizard 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 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 "Select Range" button Select range button next to the corresponding worksheet. The "Select All" button will come in handy if you want to merge the same name worksheets across all open workbooks.

Step 2: Choose how to combine the source worksheets

As you can see in the screenshot below, the Consolidation wizard provides a number of options to choose from. Since we are concerned with combining the same name worksheets, we choose exactly this option, which comes 4th in the list.
Specify how to combine the source worksheets

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

On this step, you specify how exactly 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.
For our example we do not need any additional options, so we simply choose "Paste all":
Specify how to paste data from the same name sheets to the destination workbook

Step 4. Choose the destination workbook

In fact, you have just one alternative here - a new workbook, which is the default option. So, you simply click "Finish".
Choose the destination workbook

Step 5. Enjoy the results :)

In truth, the final step does not require your participation other than waiting patiently for a few seconds while the wizard is doing the consolidation job.
The Consolidation wizard copies data from multiple worksheets into a single one

When it is done, you will see the confirmation message similar to this one:
88 Excel worksheets were successfully merged in a few seconds

As you see, 88 worksheets with the same names were sucessfully combined in under 5 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 consolidation tasks.

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

So, don't hesitate to download the fully-functional evaluation version and try it on your own data. The Consolidation Wizard 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!

See also

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,

  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

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!