Log in
x
or
x
x
Register
x

or

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.


Click Enterprise >> Combine. See screenshots:

shot combine same name sheets 1
kutools-arrow-right

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.

shot combinep sheets with same names 3

Tips:

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 shot combine same name worksheets 5 5  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:

shot combine same name worksheets 7 7
kutools-arrow-right
shot combine same name worksheets 8 8
shot combine same name worksheets 9 9
shot combine same name worksheets 10 10

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:

kutools-arrow-right

2. Click Next button, and please configure settings in the Step 3 as follows:

Tips 2:

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:

kutools-arrow-right

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.

Kutools for Excel

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.

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase

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.
  • To post as a guest, your comment is unpublished.
    Daryl D. Vorpahl · 3 years ago
    I have tried to merge as the instructions but Kutools and other tabs are not displayed at the bottom of form. Wanted to merge multiple xlsx files into one file and be able to go from tab to tab as directions show. What am I doing wrong?
    Please help.

    Thank-you,
    Daryl D. Vorpahl
    • To post as a guest, your comment is unpublished.
      admin_jay · 3 years ago
      [quote name="Daryl D. Vorpahl"]I have tried to merge as the instructions but Kutools and other tabs are not displayed at the bottom of form. Wanted to merge multiple xlsx files into one file and be able to go from tab to tab as directions show. What am I doing wrong?
      Please help.

      Thank-you,
      Daryl D. Vorpahl[/quote]
      Hello Dary, please try to download and install the Kutools for Excel from here first: http://www.extendoffice.com/download/kutools-for-excel.html

      If the problem persists, please try to contact me at jaychivo#extendoffice.com. Please replace # with @.

      I will try my best to help you. :-)