In Excel, there are many ways to copy a worksheet in the same or different workbook. In this article, we will see many processes on 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.
First of all, let`s make a worksheet first where the amount of total sales of a shop is given for different years. We will copy this worksheet in the same and different workbooks following many processes.
Copy a Worksheet in the Same Workbook Using Excel Move/Copy Feature
Excel has a built-in feature for copying a worksheet into another worksheet. To perform this follow the below steps.
- First Select the worksheet that you want to copy. Here S1 is the Worksheet that we want to copy.
- Click on the Home tab and under the Cells option click on the Format In the Format button click on the option which states Move or Copy Sheet.
- After this, you will get a new dialogue box name Move or Copy. In that box select Book1 in the To Book drop-down option and S2 in the Before sheet drop-down option.
- Now for creating a copy of the worksheet put a tick mark on the Create a copy
- After Pressing OK you will see that your worksheet gets copied before the sheet S2. If you want the copied worksheet after S2 select the move to end option instead of S2.
Copy a Worksheet in Different Workbook Using Excel Move/Copy Feature
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. The procedure is the same as before. The only difference is that instead of selecting the same workbook in the Move or Copy dialogue box you need to select a different workbook. After selecting a different workbook you will get a different worksheet’s names.
After pressing Ok in the Move or Copy dialogue box you will see your data gets copied in a different workbook.
Moving Worksheet Right by Holding the Ctrl Key
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.
- At first, click on a worksheet that you want to copy and hold. You will see a worksheet icon that will appear like the following image.
- Now press on to the Ctrl You will see a “+” sign appears after pressing the Ctrl button.
- Now holding the Ctrl button drag the worksheet in the right position. You will see a copy of your worksheet arrives in your workbook.
Copying the whole or a Portion of the Worksheet using Copy and Paste option
This is the easiest way of copying the data of 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.
- Select the portion you want to copy and right-click to select the copy option.
- If you want to select the whole worksheet you can even press Ctrl + A to select the whole worksheet and then press Ctrl + C to copy all of them. To select the whole worksheet you can also select the leftmost part of your sheet which has a triangle-shaped look in it.
- Now in a different worksheet first select the cells where you want to paste your data and then press Ctrl + V or right-click on the mouse to select the paste option.
Excel VBA to Copy Worksheet in the same Workbook
We can even use Excel VBA to copy a worksheet in the same 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 a short and easy code so that you can easily perform it. To perform the VBA code for copying and pasting follow the below steps.
- First, click on the Developer tab on top, and in there select the Visual Basic under the Code branch.
- In the Microsoft Visual Basic for Applications dialogue box, select the worksheet that you want to copy which is under the VBAProject Double-click the worksheet for inserting the VBA code.
- Now insert the below code and run it.
Dim Wrs1 As Worksheet
Set Wrs1 = ThisWorkbook.Worksheets("S1")
- After running the code, you will get to see that your worksheet gets copied in the same workbook.
The code is very simple to use. We defined the Wrsl as Worksheet. Here S1 is a variable of Wrsl. Our worksheet name is S1 and we wanted to copy that in our workbook. Type your worksheet name which you want to copy the code instead of S1. You will see the same result for this.
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 might be useful to you. Follow our blog to find more useful and interesting articles.