`
Join In
Useful Tools
Combine same name sheets into one book
Tools       2020/6/9 9:38:12          Author: FFCell
Views: 0

If we want to combine or collect same name sheets from multiple workbooks into a separate workbooks, to be exactly, different same name worksheets from mutilple workbooks will be collected into respective individual workbooks, we can use this feature: Combine same name sheets into one book.


For example, we want to combine same name sheets from below two workbooks Store A.xlsx and Store B.xlsx into one workbook.

The workbook Store A.xlsx includes two worksheets: January and February.

The workbook Store B.xlsx also includes two worksheets: January and February.

We want to combine the same name sheets (sheet January) into a new workbook, and the same name sheets (sheet February) into a new separate workbook too. Now, let's combine those same name worksheets from these two workbooks into one workbook.


We choose the feature Combine same name sheets into one book in Combine group, and then click the Start button (upper right corner).



Step 1: Select excel files


We can drag these two excel files and drop them to the blank area, or click Add files button to select those excel files you want to combine.

If there are some excel files you do not need, pls select them and then click Remove button. Click Remove all button will remove all excel files.

If we want to see the path of the excel files, pls tick Show path.

After selecting the excel files we need, and then click Next button.


Setp 2: Select worksheets


All sheets means all sheets in the selected excel files will be copied to a new workbook.

If we only want to copy the first sheet in each selected excel file, we can choose the next option and set it as :

Sheet 1 to 1 from left to right


If we want to copy specific sheets in the selected excel files to a new workbook, for example only copy sheet February, pls choose this option:Specific sheets (MultiSelect)

And we will get all sheets' names from the first excel file by default, and then tick the specific sheet (sheet February ) you want.

If the first excel file does not include sheet February, we can click Get All , and then we will get all sheets' names from all selected excel files. But it will take a long time.

It is better for us to click the Input button and then input the sheet name manually.


If we don't want the hidden sheets or blank sheets to be copied to the new excel file, pls tick Skip hidden sheets and Skip blank sheets. In Step 2, we decide to choose All sheets, and then click Next button.


Step 3: Data options

In this part, we need to set Paste options:

Paste all (values, formulas, forat etc.) means to copy all information including values, formuls, format, notes etc.

Paste values only means to copy the values only in selected sheets to the new workbook.

Paste formulas only means to copy the formulas only.

Paste values & format means to copy the values and format.

Paste formulas & format means to copy the formulas and format.

Create links to source data means that the result data will update automatically if original data changes.


After setting the data options, pls click Next button.


Step 4: Ready to combine

In step 4, we can set the new sheet name by click the Naming Convention button if you do not like the default setting. (What is Naming Convention, how to use it? pls click here)


And we can click Browse button to change the saving path of the folders for the result workbooks if we do not like the default saving path. And then click Start button. Some seconds later, combination completed.


Finally, we get a folder whose name is Result-1004 in the computer desktop. Open this folder, there are two excel workbooks: January.xlsx and February.xlsx. 

In the workbook January.xlsx, there are two worksheets whose names are Store A and Store B. 

In the workbook February.xlsx, there are also two worksheets whose names are Store A and Store B.