# How to Copy a Sheet to Another Workbook with Excel Formulas – 2 Methods

The Excel workbook contains different worksheets. This worksheet showcases the dataset below.

Calculated the Average Marks using a formula.

Use this formula to calculate the Average Marks of the first:

`=(C5+D5)/2`

You can see the formula in E5.

### Method 1 – Copy a Single Excel Sheet with Formulas to Another Workbook

#### 1.1 Drag the Mouse

To copy the DragÂ worksheet to another workbook:

Steps:

• Open the two workbooks.
• Book1 is the workbook to keep the copied sheet.

• Go to the View tab.

• Click View Side by Side.

• It will arrange the two workbooks vertically:

• Press CtrlÂ and drag the â€˜Dragâ€™ worksheet from â€˜Copying Worksheet to Another Workbook with Formulasâ€™ to â€˜Book1â€™.
• Rename it as â€˜Dragâ€™ in â€˜Book1â€™.

Note:

If you do not press Ctrl and still drag, the sheet will be copied to the destination workbook, but it will be cut from the original workbook.

This is the output.

#### 1.2 Copy and Paste Feature

Steps:

• Click the small triangle at the upper left corner of the worksheet, or press Ctrl + A.
• The whole worksheet is selected.

• Press Ctrl + C .
• Or right-clickÂ and select Copy.

• Or, click Copy in the Home tab.

• The border of the sheet is highlighted. It is copied.

• Open the second workbook and select the upper-most left cell in a Sheet.
• Here,Â  â€˜Sheet1â€™ in â€˜Book5â€™ workbook and A1.

• Press Ctrl + V.
• Or, right-clickÂ and select Paste.
• You can also select Paste in the Home tab.

This is the output.

• Check the formula in the copied worksheet:
• The formula is also copied.

#### 1.3 Use the Move or Copy Dialog Box

Steps:

• Select â€˜Move or Copyâ€™ in the source workbook.
• Right-click.
• Select Move or Copy.

• The Move or Copy box will be displayed.

• Select (new book) in To book.
• CheckÂ  Create a copy (to keep the sheet in the source workbook).

• A copy of the sheet is created in the destination workbook. Here, a copy of the â€˜Move or Copyâ€™ sheet in the source workbook is created in Book10 including the formulas.

#### 1.4 Keep the Link While Copying a Sheet with Formulas

The Average Mark of the first student is 77 (E5).

In the copied worksheet, the Average Marks is the same for the first student.

If you change the marks of Physics from 75 to 77 (C5) in the original book, the Average Mark will be 78 (E5).

But the copied worksheet will not change.

Steps:

Create a link between the original and copied workbook:

• Enter the sheet name! (here â€˜Link!â€™) before the cell references.
• The formula in E5 will be:
`=(Link!C5+Link!D5)/2`

• Copy the worksheet (Link) into a new workbook (Book14) by following Method 1.2.
• The image displays the Formula Bar for E5 in the new workbook.

• Change the marks in Physics of the first student (C5) in the original workbook.
• The Average Marks (E5) of the first student will be updated.

• Go to the new workbook (Book14).
• You will see that the Average Marks in E5 are also updated.

#### 1.5 Use Excel VBA

Steps:

• Open the workbook to copy the worksheet and the one to paste it.
• In the â€˜Sourceâ€™ workbook, copy B2:E9Â in the â€˜Overviewâ€™ worksheet.

• Enter the copied dataset in a new workbook (Book7) using Excel VBA.

• Go to the Developer tab in the original workbook.
• Click Visual Basic in Code.

• Go to the Insert tab > select Module.

• Double-clickÂ Module1.

• Enter the VBA code below in the code window:
``````Sub CopyOneFromAnotherNotSaved()
Workbooks("Source").Worksheets("Overview").Range("B2:E9").Copy _
Workbooks("Book7").Worksheets("Sheet1").Range("B2")
End Sub``````

• Go to the Run tab > select Run Sub/Userform

• You will see the dataset (B2:E9) that you copied in â€˜Sheet1â€™ ofÂ  Book7.

• Check the formula in E5.
• The dataset is copied with formulas.

### Method 2 – Copy Multiple Excel Sheets with Formulas to Another Workbook with the Move or Copy Dialog Box

#### 2.1 Right-Click the Sheet Tabs

Steps:

• See the formulas (in E5) in the original workbook.

• Click the first sheet tab (Overview), press Shift and click the last sheet tab (VBA).
• All worksheets in the workbook will be selected.
• If you do not want to copy all sheets, press Ctrl and click the sheet tabs you want to copy.

• Right-click the selection and click Move or Copy.

• The Move or Copy dialog box will be displayed.

• Choose (new book) in To book > check Create a copyÂ > click OK.

• AllÂ  worksheets will be copied to a new workbook (Book3).
• In E5, you can see the formula that was in the original workbook.

#### 2.2 Use the Excel Ribbon

Steps:

• Select all the worksheets in the original workbook by following the previous method.

• Go to the Home tab.

• Click Format in Cells.

• Select Move or Copy.

• In the Move or Copy dialog box, select (new book) in To bookÂ  > check Create a copy > click OK.

• All worksheets are copied to another workbook (Book5).

## Related Articles

<< Go Back to CopyÂ Sheet | Worksheets | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF