How to Copy or Move a Worksheet to Another Workbook in Excel

You often need to reorganize data by moving a sheet between workbooks. Excel provides several methods to copy or move a worksheet. This article explains the standard menu method, a quick drag-and-drop technique, and how to create a copy within the same workbook.

Understanding Worksheet Transfer Methods

Moving or copying a sheet changes its location from a source workbook to a destination workbook. A move permanently relocates the sheet. A copy leaves the original sheet in place and creates a duplicate. You must have the destination workbook open in the same instance of Excel. Opening files in separate Excel windows prevents the standard move or copy commands from working between them.

Prerequisites for the Operation

Both the source workbook with the sheet you want to move and the destination workbook must be open. Ensure they are opened in the same Excel application window, not as separate, independent instances. You can verify this by checking if you can use the Window menu to switch between them. The destination workbook must not be protected or shared in a way that restricts adding worksheets.

Steps to Copy or Move a Sheet Using the Menu

This method gives you the most control, including the option to create a copy and choose the exact position for the new sheet.

1. Open both workbooks
   Open the source workbook and the destination workbook. Ensure they are in the same Excel window.
2. Right-click the sheet tab
   In the source workbook, right-click the tab of the worksheet you want to copy or move.
3. Select Move or Copy
   Click ‘Move or Copy’ from the right-click menu. This opens the Move or Copy dialog box.
4. Choose the destination workbook
   In the ‘To book:’ dropdown list, select the open destination workbook. To create a new workbook for the sheet, select ‘(new book)’.
5. Select position and check the copy box
   In the ‘Before sheet:’ list, click where you want the sheet to appear. To copy the sheet instead of moving it, check the box labeled ‘Create a copy’.
6. Complete the action
   Click OK. The worksheet will be moved or copied to the specified location in the destination workbook.

Using Drag and Drop to Move a Sheet

For a quick move between two workbooks, you can use the mouse. This method is best when you have both workbook windows arranged so you can see their sheet tabs.

1. Arrange the workbook windows
   Go to the View tab and click ‘Arrange All’. Select ‘Vertical’ or ‘Horizontal’ to see both workbooks side-by-side.
2. Click and drag the sheet tab
   Click on the tab of the sheet you want to move. Hold down the left mouse button and drag the tab.
3. Drop the sheet into the new workbook
   Drag the mouse pointer over the destination workbook’s window and release the button when a small black triangle appears where you want the sheet placed. To copy instead of move, hold the Ctrl key while dragging before you release the mouse button.

Common Mistakes and Limitations

Move or Copy Dialog Shows Only One Workbook

If the ‘To book:’ dropdown in the Move or Copy dialog only shows the current workbook, the destination file is not open in the same Excel instance. Close the destination workbook. Then, from your already open Excel window, use File > Open to open the destination file. Now try the Move or Copy command again.

Formulas Reference Cells in the Original Workbook

When you move a sheet, formulas that reference other cells within that sheet update automatically. Formulas that reference cells in other sheets of the *source* workbook will still point to the source workbook. This can cause #REF! errors if the source workbook is closed. After moving, check key formulas and update references to point to the new location if needed.

Dragging a Sheet Does Not Work

Drag-and-drop between workbooks is disabled if the workbooks are opened in separate Excel processes. Use the ‘Arrange All’ feature first. Also, ensure workbook protection is not enabled, as protected workbooks often prevent sheet tab dragging.

Copy Within Workbook vs Move to Another Workbook

Item	Copy Within Same Workbook	Move to Another Workbook
Primary Use	Create a template or backup sheet inside the same file	Transfer data and logic to a different project file
Method	Right-click tab > Move or Copy, select current workbook name, check ‘Create a copy’	Right-click tab > Move or Copy, select the other open workbook from the list
Effect on Formulas	All internal references stay valid; sheet names get a number suffix like ‘(2)’	References to other sheets may break, pointing back to the closed source file
Keyboard Shortcut	Hold Ctrl, drag sheet tab to new position	No direct keyboard shortcut; requires dialog or mouse drag between windows

You can now efficiently reorganize your data across multiple Excel files. Use the Move or Copy dialog for precise control over placement and to create copies. Try the Alt key with drag-and-drop for quick reorganization between tiled windows. For advanced control, use the View > Window > Switch Windows menu to ensure all workbooks are in the same instance before starting the operation.