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

Last updated on June 24th, 2018

In Excel, there are many ways to copy a worksheet in a 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 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 in 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 to the Home tab and under the Cells option click on to the Format In the Format button click on the option which states Move or Copy Sheet.

How to Copy a Worksheet in Excel

  • 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

How to Copy a Worksheet in Excel

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

Note: The shortcut way to find the Move or Copy dialogue box is to select the worksheet you want to copy and right clicking on it to find the context menu where you can easily find the Move or copy option.

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 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 different worksheets name.

After pressing Ok in the Move or Copy dialogue box you will see your data gets copied in a different workbook.

Note: Don’t forget to put a tick on the Create a copy option while copying the worksheet in the Move or Copy dialogue box.

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 will appear like the following image.

  • Now press on to the Ctrl You will see a “+” sign appears after pressing the Ctrl button.

How to Copy a Worksheet in Excel

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

How to Copy a Worksheet in Excel

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.

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 same cell values. There are many VBA code 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.

Sub Test()

Dim Wrs1 As Worksheet

Set Wrs1 = ThisWorkbook.Worksheets("S1")

Wrs1.Copy ThisWorkbook.Sheets(Sheets.Count)

End Sub

How to Copy a Worksheet in Excel

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

Conclusion

Although you can use the normal copy and paste option for copying your worksheet we suggest to choose 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.

Related Articles

  1. How to Merge Excel Worksheets without Copying & Pasting
  2. How to Copy and Paste Multiple Cells in Excel (7 Ways)

Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
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 attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply