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):
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.
1. Applying Keyboard Shortcut Feature
Currently, we will start with the keyboard shortcut key to copy and paste our dataset to another workbook.
- 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).
- 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).
- 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.
- Moreover, check the cells if your formula remains the same. Here, we have shown cell E5.
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.
- 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.
- At this time, a Move or Copy wizard will appear just like the screenshot below.
- 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.
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.
- 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.
- 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.
📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging
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.
- To establish a link between the two worksheets, we have to insert the following formula into the source workbook.
- Then, take your cursor to the bottom-right corner of cell E5 and double-click.
- Eventually, you have successfully applied the formula to all other rows as you can see in cell E6 from the following screenshot.
- 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.
- 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.
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.
- 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.
- A new window will appear.
- From there, select Insert and go to Module.
- 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
- 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.
📝 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.
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.
- 3 Ways to Copy Formula with Relative Reference Using Excel VBA
- How to Copy Formula Down in Excel Without Incrementing
- 3 Quick Ways to Copy Formula and Paste as Text in Excel
- How to Copy a Formula in Excel with Changing Cell References
- Copy Formula and Change Only One Cell Reference in Excel