How to Copy Excel Sheet to Another Workbook with Formulas

While using Excel we sometimes need to copy Excel sheet to another workbook. Most of our worksheet contains formulas that we want to be present in the destination worksheet. In this article, we will learn about how to copy an Excel sheet to another workbook with formulas in different methods.


📁Download Excel File

Download the following Excel file to realize the topic more clearly.


Learn to Copy Excel Sheet to Another Workbook with Formulas with 5 Easy Approaches

We will work with the following sample dataset (B2:E10) to copy an Excel sheet to another workbook with formulas. It contains NBA Team names and their W-L records along with the Percentile.

Here, we determined the Percentile using the following formula (for cell E5):

=C5/C5+D5

Now, we will use the fill handle tool to apply the formula to the Percentile column. You can review the formula for cell E5 in the following screenshot.

We are all set to copy our dataset from this Excel sheet to another workbook. We will introduce 5 easy approaches to copying an Excel sheet to another workbook with formulas.


Approach

1. Applying Keyboard Shortcut Feature 

Currently, we will start with the keyboard shortcut key to copy and paste our dataset to another workbook.

⬇️⬇️ STEPS ⬇️⬇️
  • To begin with, click the small triangle in a rectangle to select the whole Excel sheet or press Ctrl+A to do the same.
  • As you can see from the screenshot below the whole worksheet is selected.

  • Next, press Ctrl+C from the keyboard. Now, we have copied the worksheet to the clipboard.
  • Or you can do the same by right-clicking on your mouse and selecting Copy (see the image below).

Applying Keyboard Shortcut Feature to copy Excel sheet with formulas to another workbook

  • There is another way to copy the worksheet. You can choose Copy from Home in the Excel Toolbar just like in the photo below.

  • Follow any of the processes and you will discover dancing Border on the highlighted worksheet (see the following screenshot). It indicates you have copied your worksheet.

  • Now, navigate to the workbook where you desire to paste the dataset. In our case, we will paste our dataset to the workbook Book2 and worksheet Sheet1.
  • Later, select cell A1 and press simultaneously Ctrl+V.

  • Or, you can right-click and Paste.
  • Furthermore, under the Home tab, you will see Paste. You can use that to paste the dataset into the new workbook (see the photo below).

utilizing Keyboard Shortcut Feature to copy Excel sheet with formulas to another workbook

  • Consequently, you can see from the following screenshot that we have successfully copied the dataset from our original Excel sheet. Accordingly, the formatting stays the same.

using Keyboard Shortcut Feature to copy Excel sheet with formulas to another workbook

  • Moreover, check the cells if your formula remains the same. Here, we have shown cell E5.

📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel


Approach

2. Utilizing Move or Copy Wizard

Now it is time for another approach to copy an Excel sheet to another workbook with formulas. From this point, we will utilize the Move or Copy wizard.

⬇️⬇️ STEPS ⬇️⬇️
  • First of all, we will take the cursor to the Move or Copy sheet of the workbook.
  • Next, right-click and click Move or Copy from the box.

using Move or Copy wizard to copy Excel sheet with formulas to another workbook

  • At this time, a Move or Copy wizard will appear just like the screenshot below.

applying Move or Copy wizard to copy Excel sheet with formulas to another workbook

  • Then, select the (new book) option from To book: drop-down menu.
  • Most importantly, you need to check Create a copy box. Otherwise, the source worksheet will disappear.
  • Lastly, click OK.

  • Voila! You have successfully copied the dataset to the Book3
  • As you can see from the following screenshot, the sheet name has changed to the source worksheet and the formulas are also identical.

using Move or Copy wizard to copy Excel sheet with formulas to another workbook


Approach

3. Using Drag and Drop Feature

Furthermore, we will see how to copy an Excel sheet to another workbook with formulas using the drag-and-drop feature. Hence, we will copy the Drag and Drop sheet to another workbook.

⬇️⬇️ STEPS ⬇️⬇️
  • First, open a new workbook where you want to paste your copied dataset. In this instance, we will copy our Excel sheet to the Book4 workbook Sheet1 worksheet as marked in the figure below.

  • Later, navigate to the source workbook from where you want to copy the Excel sheet. Click the View option from the Excel Toolbar like the screenshot below.

  • Then, click View Side by Side option.

  • The workbooks will rearrange like the screenshot below.

applying Move or Copy wizard to copy Excel sheet with formulas to another workbook

  • At this time, press Ctrl and drag the Drag and Drop worksheet from the source workbook to the destination workbook (Book4).
  • It will create a new worksheet named Drag and Drop with the same formatting and formulas.
  • You can see that the formula remains constant from the highlighted portion in the picture below.

using Drag and Drop feature to copy Excel sheet with formulas to another workbook

📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging


Approach

4. Keeping Link to the Original Sheet

From the above 3 procedures, we can just copy the Excel sheet to another workbook, albeit there won’t remain any link between the sheets.

For instance, the following figure is of our source worksheet which is named Linked.

We have copied the source worksheet to another workbook named Book1.

However, if we change the value of any of the cells in the Won or Lost column, the Percentile column of the corresponding cell will also change. Right now, we have changed the cell C6 value to 8. The cell E6 has also changed to 0.727.

But the pasted worksheet has remained all the same with the value at cell E6 being 0.700.

If you want your destination worksheet to be updated like your source worksheet, follow the next steps.

⬇️⬇️ STEPS ⬇️⬇️
  • To establish a link between the two worksheets, we have to insert the following formula into the source workbook.
=Linked!C5/(Linked!C5+Linked!D5)
  • Then, take your cursor to the bottom-right corner of cell E5 and double-click.

Keeping Link to the Original Sheet to Copy Excel Sheet with Formulas to another workbook

  • Eventually, you have successfully applied the formula to all other rows as you can see in cell E6 from the following screenshot.

using Link to the Original Sheet to Copy Excel Sheet with Formulas to another workbook

  • After that, copy the worksheet using Keyboard Shortcut Feature.
  • Momentarily, you will see the following formula on the Formula bar if you select a cell under the Percentile Here we have selected cell E6.

applying Link to the Original Sheet to Copy Excel Sheet with Formulas to another workbook

  • Now, let’s check if it works.
  • We will change our source worksheet cell C6 value to 9. Thus, cell E5 changes to 750.

  • From the destination worksheet, we can also see that cell E6 has updated to750 too.
  • Nonetheless, cell C6 will not change because we have not linked the data from the source worksheet.

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel


Approach

5. Applying Excel VBA

If you deal with a large dataset, the following method will come in handy. You can insert a simple code to do all your Copy-Paste work. We will be the following dataset to demonstrate how we can do that.

⬇️⬇️ STEPS ⬇️⬇️
  • First of all, we will open a new workbook Book2 where we want to paste our dataset.

  • Next, move to the source workbook and select the Developer tab option from the Excel Toolbar. After, click on the Visual Basic option from the Code group.

applying Excel VBA to Copy Excel Sheet with Formulas to another workbook

  • A new window will appear.
  • From there, select Insert and go to Module.

applying Excel VBA to Copy Excel Sheet with Formulas to another workbook

  • It will create a module under Project – VBAProject.
  • From there, double-click on Module1.

  • A code window will open.
  • Copy and paste the following code into the code window.
Sub CopyWithFormulasToAnotherWorkbook()
Workbooks("how to copy excel sheet with formulas to another workbook").Worksheets("Excel VBA").Range("B2:E10").Copy _
Workbooks("Book2").Worksheets("Sheet1").Range("B2")
End Sub

using Excel VBA to Copy Excel Sheet with Formulas to another workbook

  • Now, select the Run tab and RunSub/UserForm.

  • If you open Book2, it will show you the copied dataset from the source worksheet.
  • Although the formatting will remain the same, the column width and row height will not be copied from the source worksheet.
  • At last, you can crosscheck the formula from cell E5.

📕 Read More: 9 Examples to Copy Formula from Above Cell Using VBA in Excel


📝 Takeaways from This Article

  • Copying Excel sheet to another workbook with formulas applying Keyboard Shortcut Feature
  • Using Move or Copy wizard to copy Excel sheet to another workbook
  • Dragging and dropping to copy an Excel sheet to another workbook using View Side by Side
  • Keeping the link between the original and copied dataset utilizing the Formula bar
  • Inserting Excel VBA code to copy large dataset to another workbook easily.

Conclusion

That concludes the discussion for today. These are some convenient methods to copy Excel sheet with formulas. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.


Related Articles

(Visited 122 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo