How to Copy Sheet to Another Workbook with Excel Formulas

While working in Excel, it is one of the most common problems that we face is to copy the Excel sheet with formulas from one workbook to another. Today I am going to show you how to copy one or more sheets with formulas from one workbook to another with proper illustrations.

How to Copy Excel Sheet with Formulas to Another Workbook: 2 Easy Ways

Suppose we have an Excel workbook that contains some worksheets containing a dataset (B4:E9) like below. It contains the Name of some Students, their marks in Physics and Chemistry and the Average Marks in the subjects.

We have calculated the Average Marks using a formula. For example, the formula to calculate the Average Marks of the first student is:

We can see the formula in cell E5 of the screenshot below.

In this article, we will copy one or multiple worksheets in the workbook to another workbook with the formulas in the E5:E9 range. Here, we will discuss two easy ways to do so.

1. Copy Single Excel Sheet with Formulas to Another Workbook

In this method, we will copy a single Excel sheet to another workbook with formulas. We can perform this task with 5 approaches. Let’s see the approaches below.

1.1 Drag the Mouse

In the first approach, we will copy an Excel sheet to another workbook with formulas by dragging the mouse. Suppose, we need to copy the worksheet named Drag to another workbook. Follow the steps below to do so.

Steps:

• In the beginning, open the two workbooks on your computer.
• One is the workbook you want to copy and the other one is the workbook to where you want to copy.
• In our case, Book1 is the workbook where we want to keep the copied sheet (see screenshot).

• Therefore, on the Excel ribbon, go to the View tab.

• Next, click on the View Side by Side option.

• After pressing the View Side by Side option.
• It will arrange the two workbooks vertically like the picture below.

• Now, press Ctrl on your keyboard and drag the ‘Drag’ worksheet from the ‘Copying Worksheet to Another Workbook with Formulas’ workbook to the ‘Book1’ workbook.
• Eventually, it will be renamed as the same name on the source workbook as the destination workbook.
• Like in my case, it has been renamed as ‘Drag’ in the ‘Book1’ workbook.

• Finally, see in the following image that you have successfully copied one Excel Sheet from one workbook to another workbook.
• Accordingly, everything including the formulas in the source workbook has been copied to the destination workbook.

Read More: How to Copy a Sheet to Another Sheet in Excel

1.2 Copy and Paste Feature

If you do not want to follow the previous method, you can also use the Copy & Paste feature and easily copy an Excel Sheet from one workbook to another with formulas. In this case, we will copy the sheet named ‘Shortcut Key’ (see the screenshot below). The steps are below.

Steps:

• Firstly, click the small triangle in the upper left corner of the worksheet, or press Ctrl + A on the keyboard.
• Hence, you will have the whole worksheet selected like the image below.

• Next, press Ctrl + C on your keyboard.
• Otherwise, right-click on your mouse and select Copy.

• Or, click on the Copy option under the Home tab from Excel ribbon.
• See the following figure.

• As a result, you will find the border of the sheet highlighted like the image below.
• This means you have successfully copied the Worksheet.

• Now, open the second workbook (the workbook in which you want to copy the Sheet) and select the upper-most left cell in a Sheet of that workbook.
• Here, I opened ‘Sheet1’ from the ‘Book5’ workbook and selected cell A1.

• At this time, to paste the sheet you copied, press Ctrl + V on your keyboard.
• Or, right-click on your mouse and select Paste.
• Besides, you can also select the Paste option from the leftmost corner of the Home tab of the Excel ribbon(see the figure below).

• In turn, you will find everything from the ‘Shortcut Key’ sheet in the source workbook copied to Sheet1 of the destination workbook.

• Moreover, you can also check the formula in the copied worksheet.
• In the following picture, we can see that the formula is also copied properly to the new workbook.

Read More: How to Copy Excel Sheet to Another Sheet with Same Format

1.3 Apply Move or Copy Dialog Box

We can also copy a worksheet with formulas to another workbook by using the Move or Copy dialog box in Excel. Let’s say, we will copy the ‘Move or Copy’ worksheet to a new workbook (see the following figure). The steps to apply this approach are below.

Steps:

• Firstly, bring your mouse cursor to the ‘Move or Copy’ sheet tab of the source workbook.
• Now, right-click on your mouse.
• Then, select the Move or Copy option.

• Hence, you will get a small box called Move or Copy.

• After that, select (new book) from the To book dropdown menu.
• Importantly, you must check the Create a copy option (if you do not check the Create a copy option, the sheet will be lost from the source workbook. So be careful).
• So, my box now looks like this:

• After successfully following the above steps, you will find that a copy of the sheet from the original workbook has been created in your destination workbook.
• Here, in my case, a copy of the ‘Move or Copy’ sheet from the source workbook has been created in Book10 workbook including the formulas (see screenshot).

1.4 Keep Link While Copying Sheet with Formulas

By following the above methods, we can copy the Excel worksheet with formulas to another worksheet but there will be no link between the two worksheets (original & copied).

For example, in the following picture, we can see that, in the original worksheet, the Average Mark of the first student is 77 (cell E5).

Similarly, in the worksheet we copied, the Average Marks is the same for the first student.

Now, in the original workbook, if you change the marks of Physics from 75 to 77 (cell C5), then the Average Mark will be 78 (cell E5).

But, the copied worksheet will not have any effect on the change in the original workbook. It will remain unchanged (see screenshot).

The steps to develop a link while copying are below.

Steps:

• In order to create a link between the original and copied workbook, type the sheet name! (in our case ‘Link!’) before the cell references ( see screenshot).
• As a result, the formula in cell E5 will be:

• At this time, copy the worksheet (Link) in a new workbook (Book14) by following method 1.2.
• However, the following figure displays the Formula Bar for cell E5 in the new workbook.

• Next, change the marks in Physics of the first student (in cell C5) in the original workbook.
• Hence, the Average Marks (see cell E5) of the first student will be updated.

• Then, go to the new workbook (Book14).
• Forthwith, you will see that the Average Marks in cell E5 is also updated here.

1.5 Insert Excel VBA

This approach will guide you to copy an Excel sheet with formulas to another workbook by inserting VBA code. See the following steps to do so.

Steps:

• In the beginning, open the workbook form where you want to copy the worksheet and the one where you want to insert the worksheet that you copied.
• Here, from the ‘Source’ workbook, we will copy the dataset in the B2:E9 range in the ‘Overview’ worksheet.

• Then, we will insert the copied dataset in a new workbook (Book7) using Excel VBA.

• In order to enter the VBA code, first of all, go to the Developer tab in the original workbook.
• After that, click on Visual Basic in the Code group.

• Next, go to the Insert tab > select Module.

• In turn, we will see Module1 at the left side of the window.
• Now, double-click on Module1.

• Therefore, to copy the dataset (B2:E9) from the ‘Source’ workbook to the ‘Sheet1’ worksheet in the ‘Book7’ workbook, 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

• In the screenshot below, we can see the VBA code in the code window.

• Later, go to the Run tab > select Run Sub/Userform (see screenshot).

• Momentarily, you will see the dataset (B2:E9) that you copied in the ‘Sheet1’ worksheet of the Book7 workbook.

• Finally, you can also check the formula in the E5 cell of the new dataset.
• In the following screenshot, we can see that we have successfully copied the dataset with formulas.

Read More: Excel VBA to Copy and Rename a Worksheet Multiple Times

2. Copy Multiple Excel Sheets with Formulas to Another Workbook with Move or Copy Dialog Box

In the previous method, we discussed the process of copying only one worksheet to a new workbook with formulas. But in this method, we will demonstrate the process of copying multiple Excel sheets with formulas to another workbook. We will do it by using the Move or Copy dialog box in Excel. Here, we will learn two ways to open the dialog box and then use it to copy the worksheets. Let’s see the approaches below.

2.1 Right-Click on Sheet Tabs

In this approach, we will open the Move or Copy dialog box by right-clicking on the sheet tabs and then applying it for copying multiple worksheets. Here, we want to copy the 7 sheets from the following workbook (see the screenshot below). After that, we will insert them into a new workbook. The steps are below.

Steps:

• First, see the formulas (in cell E5 of the figure below) in the original workbook which you need to copy along with the worksheets.

• Now, click on the first sheet tab (Overview), press the Shift key and then click on the last sheet tab (VBA).
• As a result, all the worksheets in the workbook will be selected (see screenshot).
• However, if you do not want to copy all the sheets, then press Ctrl and click on the sheet tabs you want to copy.

• Next, right-click on the selection and click on Move or Copy.

• In turn, the Move or Copy dialog box will appear.

• Then, choose (new book) from the To book dropdown > check the Create a copy box > click OK.

• Therefore, all the worksheets will be copied to a new workbook (Book3).
• Afterward, in cell E5, we can see the formula that was in the original workbook (see screenshot).
• In this way, we can copy multiple sheets with formulas to another workbook.

Read More: How to Copy Multiple Sheets to New Workbook in Excel

2.2 Use Excel Ribbon

Here, we will use the Home tab for copying multiple Excel sheets (same as the previous approach) to another workbook with formulas. To do so, follow the steps below.

Steps:

• To begin, select all the worksheets in the original workbook by following the previous method.
• See the picture below.

• Secondly, go to the Home tab.

• After that, click on the Format drop-down in the Cells group.

• Then, select Move or Copy sheet from the dropdown menu.

• Consequently, the Move or Copy dialog box will pop up.
• At this time, like the previous method, select (new book) from the To book dropdown menu > put a tick mark in the Create a copy box > click on the OK button.

• Thus, we can copy all the worksheets to another workbook (Book5).
• See the final result in the following picture.

Download Practice Workbook

Conclusion

I hope the above tutorial will be helpful for you to copy the Excel sheet with formulas to another workbook. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Goodbye!

Related Articles

• Copy Worksheet to Another Workbook without Reference in Excel
• How to Copy Excel Sheet into Word
• [Fixed!] Move or Copy Sheet Not Working in Excel
• How Do I Duplicate a Sheet Multiple Times in Excel

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