The process of copying worksheets is a very common phenomenon among excel users. Especially, when the dataset gets quite large, we tend to copy the whole worksheet instead of values. In this article, we will see 4 smart ways how to copy a worksheet in Excel. Here we will discuss the move and copy worksheet feature, shortcuts, and Excel VBA to copy a worksheet in Excel.
Download Practice Workbook
Get the sample file and practice by yourself.
4 Smart Ways to Copy a Worksheet in Excel
First of all, let’s make a worksheet first where the amount of Total Sales of a shop is given for different years. We named the worksheet Dataset.
We will copy this worksheet in the same and different workbooks following the processes below.
1. Copy a Worksheet with Move/Copy Feature in Excel
Excel has a built-in feature to copy worksheets. It is applicable for both the same and different workbooks. Let’s see the processes for both.
1.1. In Same Workbook
Here is the procedure to copy a worksheet in the same workbook.
- First, choose the worksheet that you will copy later.
- Here, we will copy the Dataset worksheet.
- Then, go to the Home tab, and click on Format under the Cells option.
- Afterward, in the context menu, click on the option Move or Copy Sheet.
- After this, you will see the Move or Copy dialogue box.
- In that box, select Copy a Worksheet.xlsx in the To book drop-down option.
- Along with it, select Dataset in the Before sheet drop-down option.
- If you want the copied worksheet after Dataset, select the move to end option instead of Dataset.
- Now for creating a copy of the worksheet, put a tick mark on the Create a copy.
- After that, press OK and you will see that your worksheet gets copied before the sheet Dataset.
- Alternatively, the shortcut way to find the Move or Copy dialogue box is to select the worksheet you want to copy and right–click on it to find the context menu where you can easily find the Move or Copy option.
1.2. Among Different Workbooks
In the previous example, we saw how to copy a worksheet in the same workbook. Now following the same process, you can also copy the same worksheet in a different workbook. Let’s see how it works.
- First, open the Move or Copy dialogue box as before.
- Then, select a different workbook name in the To book section.
- After this, mark check the Create a copy box.
- Lastly, press OK and you will see your data gets copied into a different workbook.
- How to Copy Multiple Sheets in Excel to New Workbook (3 Methods)
- Copy Excel Sheet to Another Sheet with Same Format
- How to Copy and Paste in Excel Without Changing the Format
2. Drag Cursor to Copy a Worksheet in Excel
This is the easiest way of copying a worksheet which most of us don’t know. This process involves dragging the worksheet and moving it to the right side by holding the Ctrl key. The process of doing that is stated below.
- First, click on a worksheet that you want to copy and hold.
- Right after this, you will see a worksheet icon that will appear like the following image.
- Now, press the Ctrl button on your keyboard and you will see a Plus (+) sign appears after pressing Ctrl button.
- Afterward, holding the Ctrl button drag the worksheet to the right position.
- Finally, you will see a copy of your worksheet arrives in your workbook.
3. Duplicate Whole or Portion of Worksheet with Copy-Paste Option
This is the easiest way of copying the data from an Excel worksheet. You can copy the data in the same worksheet and you can even do it for a different workbook. The steps for this are given below.
- First, if you want to select the whole worksheet, press Ctrl + A to select the whole worksheet.
- Otherwise, you can also select the leftmost part of your sheet which has a triangle-shaped look to it.
- Then, press Ctrl + C on your keyboard or press right-click > Copy to copy all of them.
- Now, in a different worksheet first, select the cells where you want to paste your data.
- Afterward, press Ctrl + V or right-click on the mouse to select the Paste option.
- Finally, you will get the copied worksheet with values.
4. Apply Excel VBA to Copy a Worksheet
We can even use Excel VBA to copy a worksheet in the same or another workbook. This VBA code will actually duplicate the existing worksheet by making it a similar worksheet with the same cell values. There are many VBA codes available to perform this sort of task you can choose any of them. In this example, we tried to use some short and easy codes so that you can easily perform them. To do the task, follow the steps below.
4.1. In Same Workbook
- First, click on the Developer tab on top, and there select Visual Basic under the Code branch.
- In the Microsoft Visual Basic for Applications dialogue box, select Module from the Insert section.
- Now, insert the below code in the Module window.
Sub Copy_Before_AnySheet__inSameWorkbook() Sheets("Dataset").Copy Before:=Sheets("Dataset") End Sub
- Press Ctrl + S to save it.
- Next, click on the Run Sub/UserForm option or press F5 on your keyboard.
- After this, select Run on the Macro window.
- Finally, after running the code, you will get to see that your worksheet gets copied into the same workbook.
4.2. From Another Workbook
Here we will apply a different code to copy the worksheet to another workbook. For this, follow the procedure below.
- First, open the Module as described above.
- Then, type this code in the Module window.
Sub Copy_Sheet__To_sample() Sheets("Dataset").Copy Before:=Workbooks("sample.xlsm").Sheets(1) End Sub
- Following, save the code and close the window.
- Next, go to the Developer tab and click on Macros under the Code group.
- Lastly, select the Macro name from the list.
- Along with it, select the workbook name in the Macros section from which we will copy the worksheet.
- Finally, press Run and you will get the worksheet in a new workbook.
- To copy adjacent sheets, press Shift to select them and then follow any of the methods above.
- To copy non-adjacent sheets, press Ctrl and click on each worksheet that you want to copy. After that, apply any of the solutions above.
Although you can use the normal copy and paste option for copying your worksheet we suggest choosing different options if your worksheet contains a large data or many formulas. Hope this article on how to copy a worksheet in Excel might be useful to you. Follow ExcelDemy to find more useful and interesting articles.