How to combine worksheets of the same name into one worksheet?
In Excel, you may frequently face the need to consolidate multiple workbooks into a single master workbook. However, a common challenge arises when these workbooks contain worksheets with identical names. If your goal is to merge data from worksheets that share the same name into their respective single worksheets, this guide will show you how to streamline this process effectively.
Combine worksheets of the same name into one worksheet with Copy and Paste command
Combine worksheets of the same name into one worksheet with Kutools for Excel
Combine worksheets of the 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 that 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 worksheet data into the master worksheets.
Combine worksheets of the 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 that 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.
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 the following screenshots:
After installing Kutools for Excel, please do as following steps:
1. Click Kutools Plus > Combine, see screenshot:
2. In the Combine Worksheets wizard, select the Combine all same name worksheets into one worksheet option. See screenshot:
3. Click the Next button, on step 2 of the 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 the Add button to insert the files you want to combine. You can click the 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 the Next button on step 3 of the wizard, and specify the following settings as your need, see screenshot:
5. After finishing the settings, click the 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 the 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 the Scenario button to choose the name of the scenario you need on the step2 wizard.
6. Then the data in the same name worksheets has been merged separately into their worksheet and each worksheet information has been inserted into the first cell of the data as comments. At the same time, the different worksheets also is copied to the new workbook with its name. see screenshots:
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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!