Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to combine worksheets of same name into one worksheet?

Usually, you often need to combine multiple workbooks into one master workbook in Excel, but in some situation, you may suffer this problem: supposing you have multiple workbooks and the workbooks contain some identical worksheet names, here you only need to merge the same worksheet names data into their separate worksheet. How could you do?

Combine worksheets of same name into one worksheet with Copy and Paste command

Combine worksheets of same name into one worksheet with Kutools for Excel

arrow blue right bubble Combine worksheets of same name into one worksheet with Copy and Paste command

It seems there is no other simple method except using the Copy and Paste command to combine the data from the same worksheet names.

First, open all of the workbooks which contain the identical worksheet names that you want to merge, then copy the data from other same name worksheets, and then paste the data into the master worksheet that you want to be combined. With this repeated copy and paste operation, you can copy all the same name worksheets data into the master worksheets.

arrow blue right bubble Combine worksheets of same name into one worksheet with Kutools for Excel

The Copy and Paste command is the easiest way to combine the data from the same name worksheets, but if there are hundreds of same name worksheets need to be merged, this method will be time consuming and tedious.

But with the Combine utility of Kutools for Excel, you can quickly merge multiple same name worksheets into master worksheets.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

For example, I have two workbooks (BookA and BookB), BookA contains four worksheets (Grade1, Grade2, Grade3, Grade4) and BookB contains three worksheets (Grade1, Grade2, Grade3), and now, I can combine the data in worksheets Grade1 form the two workbooks into a new workbook which continue with the same worksheet names, so does other same name worksheets. See following screenshots:





After installing Kutools for Excel, please do as following steps:

1. Click Enterprise > Combine, see screenshot:


2. In the Combine Worksheets wizard, select Combine all same name worksheets into one worksheet option. See screenshot:


3. Click Next button, on step2 of wizard, all the opened workbooks and worksheets are listed in the list box, if you want to add other workbooks or folders, you can click Add button to insert the files you want to combine. You can click button1 button to select the range that you want to use. (By default, the used range is selected for each worksheet) See screenshot:


4. Then click Next button, on step3 of wizard, specify the following settings as your need, see screenshot:


5. After finish the settings, click Finish button to combine the same name worksheets. And you will be asked if you want to save this scenario, if you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshots), if you don’t want to save this scenario, please click No. When you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard.

doc-merge-same-name-worksheets8 -2 doc-merge-same-name-worksheets9

6. Then the data in the same name worksheets has been merged separately into their own worksheet and each worksheet information has been inserted into the first cell of the data as comments. At the same time, the different worksheet also is copied to the new workbook with its own name. see screenshots:

doc-merge-same-name-worksheets1 doc-button2



If you want to know more about this feature, please click Combine all same name worksheets into one worksheet.

Related articles:

How to combine multiple workbooks to one workbook in Excel?

How to merge worksheets / workbooks into one worksheet?

How to summarize data from worksheets / workbooks into one worksheet?

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.