If you have to entirely replicate a worksheet, copy-pasting can be a time-consuming task. Instead of the conventional Ctrl + C/Ctrl + V, you can use one of Excel’s features to move or copy your data to a different worksheet, or even a workbook.
Copy to New Worksheet in the Same Workbook
These methods are if you want Excel to duplicate your current worksheet in the same workbook. We have two methods enlisted under this section. You can use any one of these methods to copy your data from the existing worksheet to a new worksheet.
Once your sheet is duplicated, Excel will give your new sheet the same name as the original sheet with “(2)” symbolizing that it is a copy. For example, if my original sheet was named Sheet1, Excel will name the replica as Sheet1 (2).
Method 1: CTRL + Drag
In this method, we will hold down the CTRL key and drag the original worksheet into the worksheet view. Then, we will drop it in the position we wish to place our new worksheet.
Method 2: Use the Move or Copy Tool
If you’re not as interested in trying the first method, we have another method for you to test out. You can also use this tool to copy the worksheet to another workbook (more on that in the later sections!).
- Right-click on your worksheet in the worksheet view.
- Choose Move or Copy.
- In the Move or Copy window, determine the placement of your new worksheet under Before Sheet. Excel will place your sheet before the sheet you select.
- Check the box next to Create a copy.
- Select OK.
Copy to an Existing Worksheet in the Same Workbook
Let’s assume you have a list of entries in different worksheets and wish to create a master worksheet which all the data. You can use the VSTACK function in order to vertically stack your data on top of each other in an existing, or even a new worksheet.
Here’s a little example to help you understand a bit more about the application of VSTACK.
We have two worksheets, “Day 1” and “Day 2” in our workbook. I will be using VSTACK to copy content from Day 1 to Day 2 using the following formula:
=VSTACK('Day 1'!A3:F13)
Look at how conveniently Excel spills my data table from the first sheet. Now it is important to remember that the formula is dependent on the source to return the value. Therefore, DO NOT delete the source worksheet.
If you must, however, convert these formulas to values. Copy your cell content, right-click on the same area, and hit the V key from your keyboard.
Copy Worksheets from Another Workbook
Now that we’ve enlisted ways you can copy worksheets inside a workbook, let’s delve a bit into how we can replicate worksheets between different workbooks.
While VSTACK supports 3D referencing, what I’ve experienced is that the formula sometimes ends up being very long. There’s nothing with a longer formula, but you may leave room for syntax errors. Additionally, Excel could also run slow if you have a number of such longer formulas.
We will be using the Get Data tool from the Get & Transform Data section in the Data tab to copy worksheets from a different workbook.
- On the destination workbook, select a worksheet and head to the Data tab.
- Click on Get Data > From File > From Excel Workbook.
- Browse to the location of the source file and select it.
- Click Import.
- From the Navigator window, select the sheet or element you wish to copy from the workbook.
- Select Load.
Just like VSTACK, this method is dynamic. In order to break links between the two workbooks, you need to delete the connection. In the Queries and Connection sidebar, right-click on the sheet name and select Delete.
Excel will then display a confirmation box. Click on Delete again so that you de-establish the link between the two workbooks.