Working with Multiple Workbooks

In Chapter 4, you moved a range of cells from one worksheet to another. You can also transfer data between two workbooks. For example, if the people at Tip Top Roofing wanted to replicate the structure of their 2002 invoice workbook for a 2003 invoice workbook, they could display two workbooks and copy the existing structure to the new workbook, saving them a lot of typing and formatting time in the process. In this topic, we ll show you how to display multiple workbooks and then how to copy or move information between them. Before we finish this topic, we ll go over the steps of deleting sheets that you no longer need.

Displaying More than One Workbook

Before you can experiment with moving and copying information between workbooks, you need to set up your workspace. Follow these steps to display two workbooks on your screen:

1. If necessary, start Excel. Then click the Open button on the Standard toolbar, and double-click 2002 Jobs to open that workbook.

2. Click the New button on the Standard toolbar.

   Excel displays a new workbook, assigning it the name Book and the next sequential number for this session ( Book2 , unless you have opened additional workbooks while working through this chapter).

   

3. To see both workbooks at the same time, click Arrange on the Window menu.

   Excel displays the Arrange Windows dialog box shown here:

4. Click OK to accept the default Tiled option.

   Excel arranges the empty workbook and 2002 Jobs side by side on your screen.

Opening a recently used workbook

When the New Workbook task pane is displayed, you can click the name of a recently used workbook in the Open a workbook area of the task pane.

 

Copying an Entry Between Workbooks

Copying entries between workbooks works the same as it does within a single worksheet or between worksheets in a single workbook. You can use the Copy and Paste commands or buttons , or you can use drag-and-drop editing. Try this:

1. Activate 2002 Jobs by clicking its title bar.

2. Select A12:D24, and click the Copy button.

3. Activate Book2 , and click in cell A1 .

4. On the toolbar, click the Paste button.

   Excel faithfully pastes a copy of the selected range into cells A1:D13 in Book2 and displays the floating Paste Options button at the bottom of the pasted range. The selected range in 2002 Jobs is temporarily outlined by a moving dotted line to show that it is the source of the copy action.

5. Click the floating Paste Options button.

   A menu drops down below the button, as shown here:

   

6. Click Keep Source Column Widths on the menu.

   Excel applies the column-width settings of the original copied cells to the paste area.

7. Press Ctrl+Home to move to cell A1, and click the Save button.

8. Activate 2002 Jobs , and press Enter to finalize the copy action.

   The moving dotted line surrounding the selected range changes to a solid border.

Moving a Worksheet to a Different Workbook

You can easily move a worksheet from one workbook to another. For this example, you ll move a worksheet from Book2 to 2002 Jobs . Before you do this, let s rename a couple of the worksheet tabs so that you can easily identify the worksheet you re going to move. Follow these steps:

1. In the 2002 Jobs window, double-click the Sheet1 tab to select its name.

2. Type Invoices 2002 , and press Enter .

   Excel displays the new name on the tab.

3. Activate the Book2 window. Then double-click the Sheet1 tab, type Invoices 2003 as the new name, and press Enter .

4. Click the Invoices 2003 tab, and hold down the left mouse button.

   The pointer becomes an arrow with a sheet attached to it.

5. Drag the sheet pointer until it sits between Invoices 2002 and Sheet2 of the 2002 Jobs workbook.

   Excel indicates with an arrowhead where it will place the selected sheet.

6. Release the mouse button.

   The Invoices 2003 sheet now appears in 2002 Jobs , as shown in this graphic:

   

Copying and moving sheets

You can copy a sheet within your current workbook or to another workbook. Display the sheet you want to copy, and click Move or Copy Sheet on the Edit menu. In the To book list box of the Move or Copy dialog box, specify the destination workbook. In the Before sheet box, select the sheet that will follow the copied one. Then select the Create a copy option, and click OK.

 

Deleting a Worksheet

Now suppose you want to delete the sheet you just moved to 2002 Jobs . Excel will delete the sheet permanently ”there is no safety net (you can t undo a deletion). So before you delete a sheet from a workbook, you should always do a quick visual confirmation to verify that you are sending the correct sheet into oblivion. Let s go:

1. Right-click the active Invoices 2003 sheet tab, and click Delete on the shortcut menu.

   If the worksheet is blank, Excel deletes it. If the worksheet contains entries, Excel displays a warning.

2. If Excel displays a warning, click Delete in the message box to remove the Invoices 2003 workbook from the 2002 Jobs workbook.

3. You don t need Book2 anymore, so click its title bar to active the workbook, and then close it by clicking the Close button at the right end of its title bar.

4. When Excel asks you if you want to save the changes to Book2 , click No .

5. Maximize the 2002 Jobs window, and display the Invoices 2002 worksheet.

   You might have to click the Previous Sheet button.

6. Press Ctrl+Home.

   Information about  

   Previous Sheet button, page 100