How to Copy a Worksheet in Excel (4 Smart Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Copy a Worksheet in Excel


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.

Copy a Worksheet with Move/Copy Feature in Excel

  • Afterward, in the context menu, click on the option Move or Copy Sheet.

Copy a Worksheet with Move/Copy Feature in Excel

  • 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.

Copy a Worksheet with Move/Copy Feature in Excel


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.

Copy a Worksheet with Move/Copy Feature in Excel

  • After this, mark check the Create a copy box.

  • Lastly, press OK and you will see your data gets copied into a different workbook.


Similar Readings


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.

Drag Cursor to Copy a Worksheet in Excel

  • 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.

Duplicate Whole or Portion of Worksheet with Copy-Paste Option

  • 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.
Note: This process takes a long time to copy the worksheet and if your worksheet contains any formulas the copy-paste function does not work swiftly.

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.

Apply Excel VBA to Copy a Worksheet

  • 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

Apply Excel VBA to Copy a Worksheet

Here, type your worksheet name which you want to copy the code instead of Dataset. You will see the same result for this.
  • 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

Apply Excel VBA to Copy a Worksheet

Here, you can type your required workbook name instead of sample.xlsm in the code.
  • 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.


Additional Tips

  • 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.

Conclusion

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.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1 Comment
  1. This is one of the cases I has been having difficulties in. Thanks for your explanations

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo