How to Copy a Sheet to Another Workbook with Excel Formulas

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.