Quickly merge / combine all worksheets of same name across workbooks into one worksheet
If you have several workbooks which contain multiple worksheets of same name, and you want to merge all workbooks of same name into one worksheet but also merge all the other different name worksheets from workbooks into same final workbook.in Excel, how can you quickly merge or combine all worksheets of same name from different workbooks into one worksheet in Excel? Kutools for Excel’s Combine Worksheets (Combine all same name worksheets into one worksheet) utility help you to:
Merge / combine all worksheets of same name into one worksheet
Note: The Combine all same name worksheets into one worksheet function of Combine Worksheets utility will not only merge all worksheets of same name into one worksheet but also merge all the other different name worksheets from workbooks into same final workbook. If you want to only combine all same name worksheets into one worksheet, please refer to Combine worksheets of same name into one worksheet.
Enterprise >> Combine. See screenshots:
Merge / combine all worksheets of same name into one worksheet
Supposing you have 3 workbooks (Workbook A, Workbook B and Workbook C) which all contains worksheet (Sheet1) and you want to merge all 3 worksheets (Sheet1) into one worksheet from Workbook A, Workbook B and Workbook C as well as merging the other different name worksheets of those 3 workbooks into the same final workbook, you can get it done as follows:
1. Please click Enterprise > Combine > Combine all same name worksheets into one worksheet, and you will need to spend a little time on getting know the rest steps from here on.
A. Add workbooks or csv files to merge
Click Add > File, you can add one workbook or multiple workbooks into Workbook list to merge;
Click Add > File, open the folder where the files are, specify the file type as (*.csv) or (*.xls; *.csv) to add CSV files to merge;
Click Add > Folder and specify a folder, it will automatically add all workbooks of the folder into Workbook list to merge.
B. Save or access one scenario of your operation:
If you want to save the settings of Step 2 in Combine Worksheets for future operations, you can save a screnario of the settings by clicking Scenario > Save… button. By doing so, you don’t need to add workbooks or specify the worksheets again and again in the future, you just need to open the scenario item you have saved, then all the workbooks will be listed in the Workbook list.
C. Sort all listed workbooks and worksheets in the dialog:
From the Sort drop down list, you can sort the listed workbooks and worksheets by their name order, default order, directory order or custom order to your need.
D. If you want to delete a workbook from the Workbook list, please click on the workbook and then click button, it will remove the workbook from the list immediately.
E. What’s the Same sheet button? You can see there is a Same sheet button in the dialog box. With this button, you can easily select all worksheets with same worksheet name across all the checked workbooks in the Workbook list. For example, there are 3 workbooks that have been checked in the Workbook list, if you click on the Sheet2 as shown in the below screenshot, it will immediately select all worksheets with same worksheet name across the 3 checked workbooks after clicking Same sheet button. See screenshots:
F. What’s Same range button? It will automatically specify each worksheet of all checked workbooks in the Workbook list of having the same range as the worksheet you selected. For example, The Sheet1 of Workbook C has a range $A$1:$C$6 as shown in the below screenshot, if you first click on Sheet1 and then click Same range button, you will see other worksheets of Workbook C will have the same range as the Sheet1, and each worksheet of checked workbooks in the Workbook list will also have the same range. See screenshots:
2. Click Next button, and please configure settings in the Step 3 as follows:
What is the Title row numbers? If your data has the title row as shown in the below screenshot:
If you specify the Title row numbers as value “0” for the above data, it will have the results as shown in the below screenshot. It will keep all the first rows of each worksheet in the final worksheet.
If you specify the Title row numbers as value “1”, the results will be as shown in the below screenshot. It will use one row as the title row and delete the first row of all the other worksheets after combining.
If you want to insert the worksheet information, you can insert the worksheet information in the first row of each range as well as formatting as comment styles.
|Insert worksheet information in a new row||Insert worksheet information as comments|
The other options in this step are easy for you to understand except the Paste link to source data option. This option will update automatically the combined (data) worksheets when the source data are changed.
3. After clicking Finish button to combine all worksheets of same name. And you will be asked whether you want to save this scenario if you have not saved the scenario in the Step 2. See screenshots:
4. Click OK to save the scenario, all worksheets of same name will be combined into one worksheet. See screenshots:
All worksheets (Sheet1) of workbook have been combined into one worksheet:
All worksheets (Sheet2) of workbook have been combined into one worksheet:
If you want to only combine all worksheets of same name into one worksheet, please refer to Quickly combine multiple worksheets from workbooks into one worksheet.
Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?Office Tab
: Using handy tabs in your Office
, as the way of Chrome, Firefox and New Internet Explorer.Kutools for Excel
: More than 200 Advanced Functions for Excel
2016, 2013, 2010, 2007 and Office 365.Classic Menu for Office
: Bring back familiar menus to Office
2007, 2010, 2013, 2016 and 365, as if it were Office 2000 and 2003.
The functionality described above is just one of 200 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.