How to insert worksheets from another workbook?
Sometimes you may need to import worksheets from other workbooks. If you copy the worksheets' content and paste to current workbook, it loses some formatting styles, such as row height, etc. This article describes some handy ways to insert worksheets from other workbooks quickly with losing data and formatting styles.
- (6 steps) Insert worksheet from another workbook with Move or Copy command
- (3 steps) Insert worksheet from other workbooks with Kutools for Excel
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
The Move or Copy command can help you insert worksheets from another workbook easily.
Step 1: Open all workbooks that you will work with.
Step 2: Active the workbook containing the worksheet that you will copy.
Step 3: Select the worksheets that you want to copy.
You can select multiple worksheets by holding down Ctrl key or shift key.
Step 4: Right click the selected worksheet, and click Move or Copy from contend menu.
Step 5: In Move or Copy dialog box, select the workbook that you will copy selected worksheets to in the drop down list Move selected sheets of To Book:, and check the Create a copy option.
Step 6: Click OK.
Then you will see the selected worksheets are copied and inserted to the workbook that you specified in Step 5.
1. You must open all workbooks that you will work with, including the workbooks containing needed worksheets, and the workbook you will insert the worksheets to. Otherwise, you will not find out the workbook names in Step 5.
2. You must check the Create a copy option in Move or Copy dialog box, otherwise you will move the selected worksheet out of original workbook.
The second trick, the Combine utility of Kutools for Excel, will simplify you work to insert worksheets from many workbooks without opening all of them.
Step 1: Click the Enterprise > Combine, see screenshot:
Step 2: In the Combine Worksheets dialog box, you may check one of the combine operation from the options. Here we take the third option for example, then click Next. See screenshot:
Step 3: You can add workbooks by clicking Add button or Scenario button. And then you can specify which worksheets you want to insert from the added workbooks in the right box. Also, you can rearrange the order of the workbooks and worksheets. (By default, all worksheets are selected in the Worksheet list.)
Step 4. You can rename the worksheet name and specify the Separator in the Combine Worksheet –Step 3 of 3 dialog. And also when you encounter blank worksheet, you can choose skip or keep it in the drop-down box. Finally, click Finish. See screenshot:
Then you will see all selected the worksheets are inserted into current workbook quickly. The first worksheet named Kutools will be a sheet list the detail information of each inserted worksheet. See screenshot:
Moreover, the utility of Combine also can combine worksheets into one worksheet, combine the same name worksheets into one worksheet and consolidate values across workbooks into one worksheet.Click to know more about this Merge Workbooks utility of Kutools for Excel.