`
Join In
Useful Tools
Combine each book into one sheet
Tools       2020/6/9 9:35:21          Author: FFCell
Views: 0

If we want to combine multiple worksheets in each workbook to a new worksheet, and this new worksheet will be placed in the original workbook, pls use this feature: Combine each book into one sheet.


For example, we want to combine the worksheets in each workbook below into one new worksheet. The new worksheet must be added in each original workbook.


Now, let's do it. We choose the feature Combine each book into one sheet in Combine group, and then click the Start button (upper right corner).


Step 1: Select excel files


We drag those two excel files and drop them to the blank area. After selecting the excel files, pls click Next button.


Step 2: Select worksheets


All sheets means all sheets in each selected excel file will be copied to a new worksheet.

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 each selected excel file to a new worksheet, 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 worksheet, 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 step, we have 4 parts to set: Copy range, Paste options, To Paste the copied ranges and Advanced options.


In the part of Copy range:

Whole sheet means it will copy all contents in the selected worksheets to the result worksheet.

If we want only one table header and one table footer in the result worksheet, pls tick Has table header. And set it as Has table header 2 rows, table footer 1 rows.


If we only want to copy specific range of the selected sheets, for example range A2:E7, pls choose the next one, and set it as Range: From cell: A2 to cell: E7.

If the data region in selected sheets begin from the third row, but they end in different row, pls choose the last one and set it as Range: From row from top 3 to row from bottom 1 of data region


In the part of 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.


In the part of To paste the copied ranges:

Vertically means to paste the copied ranges from up to down.

Horizontally means to paste the copied ranges side by side.


In the part of Advanced options:

AutoFit row height & column width means to autofit the row height and column width after copy.

Preserve comments will copy the comments to the result worksheet.

After setting the data options, pls click Next button.


Step 4: Ready to combine

In this step, we need to name the new worksheet. The default new sheet name is Result, but you can input the new sheet name manually by yourself.

If you want the new worksheet to be placed in the first in each original workbook, pls set Location: in the first.

If you want the new worksheet to be placed in the last in each original workbook, pls set Location: in the last.


If the original workbook exists the same name sheet as the new sheet, you can tick Replace same name sheet. And the old sheet will be replaced by the new sheet. Let's click Start button. Combination completed in some seconds.


Now, let's open each original workbook and have a look.