Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Copy a Sheet in Excel (5 Easy Ways)

Anyone may need a backup or copies of their sheet because sometimes in the same sheet we need to perform various tests, functions, etc. Datasheets can be correlated; we may need to create a new sheet depending on the existing one. Also, we may need a backup of our sheet to use later. In this article, I’m going to explain how to copy a sheet in Excel. You will know almost all possible ways of copying a sheet in Excel.


Download Practice Section

You can download the workbook to practice yourself.


5 Easy Ways to Copy a Sheet in Excel

In my workbook, I used 2 sheets. These are Sales and Product.

How to Copy a Sheet in Excel

Above dataset contains the data of Product, Sales and Order Date.

The Product Sheet contains the information of Sales Person, Product Name and Product Price.

Now, we will show you 5 easy ways to copy these sheets in Excel.


1. Copy a Sheet in Excel by Dragging

In the first method, we will show you how to copy a sheet by dragging it in Excel. You can use this method to copy a sheet within the same workbook and to another one.


1.1 Within Same Workbook

To copy an existing sheet within the same workbook in Excel by dragging follow the steps given below.

Steps:

  • Firstly, go to Sheet tabs and select the Sheet you want to copy. Here, we will select the Sales sheet.
  • Then, hold the Ctrl key.
  • After that, while holding the key drag the Sheet using your left mouse key or touchpad.

How to Copy a Sheet in Excel by Dragging

  • Now, drag it to where you want to place your copied Sheet.
  • Don’t forget to press Ctrl otherwise, it will move the sheet place and won’t copy.


1.2 To Another Workbook

To copy a sheet from one workbook to another by dragging, open both the workbooks from where you want to copy the dataset and the one where you want to copy. After that, go through the steps given below.

Steps:

  • Firstly, go to the Sheet tabs of the sheet from where you want to copy the dataset.
  • Next, select the Sheet you want to copy then hold the Ctrl key.

How to Copy a Sheet in Excel by Dragging to Another Workbook

  • Now, drag it by using the left mouse key to another workbook Sheet tab.
  • Then, place the icon where you want to place the sheet.
  • Next, you can see the same Sheet in a different workbook.

Read more: How to Copy Excel Sheet to Another Sheet


2. Right-Click on Mouse to Copy an Excel Sheet

You can also copy an Excel sheet by Right-clicking on the mouse.

2.1 Within Same Workbook

Here, you will find the way to copy an Excel sheet within the same workbook by right-clicking on the mouse.

Steps:

  • In the beginning, select the Sheet then click on the right side of the mouse.
  • Now, select Move or Copy.

Right-Click on Mouse to Copy an Excel Sheet

  • After that, a dialog box will pop up.
  • From there from the Before sheet select the sheet you want to copy.
  • Here, we have selected the Sales sheet.
  • Then, mark on the Create a copy.
  • Finally, click OK.

  • Thus, You will find the copied sheet Sales (2).

How to Copy a Sheet in Excel


2.2 To Another Workbook

To copy Excel Sheet by right-clicking to another workbook follow the steps given below.

Steps:

  • To start with, select the Sheet then click on the right side of the mouse.
  • Then, select Move or Copy.

Right-Click on Mouse to Copy an Excel Sheet to Another Worksheet

  • Next, a dialog box will pop up.
  • Now, from To book select the book name you want to place on your copied sheet.
  • From there from the Before sheet select the sheet you want to copy then mark on the Create a copy.
  • Here, we selected the workbook Book1 and the sheet Sales.
  • Finally, click OK.

  • Thus, you can copy a sheet to another workbook.


3. Use Ribbon to Copy a Sheet in Excel

Now, we will show you how you can use ribbon to copy a sheet in Excel.

3.1 Within Same Workbook

To copy a sheet using ribbon within the same workbook follow the steps given below.

Steps:

  • In the beginning, go to the Home tab >> expand Cells >> click on Format >> select Move or Copy Sheet.

How to Use Ribbon to Copy a Sheet in Excel

  • Now, the Move or Copy Sheet dialog box will pop up.
  • After that, you can select the place where you want to put your copied sheet in the Before Sheet.

  • Here, we selected Sales so the copied sheet is placed before Sales as Sales (2).


3.2 To Another Workbook

To copy a sheet in another workbook using ribbon, follow the same steps shown above. Then, in the Move or Copy box follow the steps given below.

Steps:

  • From To book you can select the workbook where you want to place your copied sheet.
  • Now, you can select the place where you want to put your copied sheet from Before Sheet.
  • Here, we selected Book1 in To book and move to end in the Before sheet.
  • Finally, click on OK.

  • Thus, the Copied sheet of Sales will be now placed in a new workbook Book1.


Similar Readings:


4. Copy an Excel Sheet Using VBA

VBA can also be used to copy an Excel sheet. Follow the steps given below to do that.

Steps:

  • Firstly, select the sheet you want to copy.
  • Then, go to the Developer tab>> click on Visual Basic.

How to Copy an Excel Sheet Using VBA

  • After selecting Visual Basic, the Microsoft Visual Basic Application window will pop up.
  • Now, click on Insert and select Module.

  • After that, in the Module write code to copy sheets in Excel.
Sub Copier()
Dim x As Integer
x = InputBox("How many copies do you want?")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Product").Copy _
After:=ActiveWorkbook.Sheets("Product")
Next
End Sub

Code Breakdown

  • Firstly, we created a Sub Procedure named Copier and declared x as Integer.
  • Then, we inserted an InputBox.
  • After that, we used a For loop to copy the dataset.

  • Now, click on the Save button to save the code and go back to the Excel Workbook.

  • Then, go to the Developer tab >> click on Macros.

  • Afterward, a dialog box will pop up.
  • Here the macro we created is Copier. From Macros, you can select the workbook where you want to run the code.
  • Then, we selected my working workbook Copy a Sheet in Excel.
  • Finally, click on Run.

Opening Macro Box to Copy a Sheet in Excel

  • A new dialog box will pop up.
  • Here, it will show “How many copies do you want?” from where you can select the number of copies you want.
  • Then, click on OK.

  • Here, we selected 1.
  • Thus, it will show one copy of the Product sheet.

Read more: Excel VBA to Copy Multiple Sheets to New Workbook


5. Duplicate a Sheet in Excel Web Version

In case anyone is using online Excel and wants to know how to copy a sheet there. Please follow the steps given below.

Steps:

  • To start with, select the sheet you want to copy then click on the right side of the mouse.

How to Copy a Sheet in Excel Web Version

  • Now, select duplicate.

  • Finally, it will copy the sheet before to your selected sheet.


How to Copy Multiple Sheets in Excel

The method we used to copy a single sheet can be applied to copy multiple sheets. But for that, you will need to select multiple worksheets.

To select multiple sheets, you can use Shift or Ctrl. To use Shift, first select the first sheet while holding the Shift key select the last sheet. All sheets will be selected. On the other hand, to use Ctrl while holding Ctrl select the sheets you want to select it won’t select all sheets.


1. Copy Multiple Sheets in Excel by Dragging

Here, you will find the way to copy multiple sheets in Excel by dragging.

1.1 Within Same Workbook

You can follow the steps given below to copy multiple sheets in Excel by dragging within same workbook.

Steps:

  • Firstly, select the sheets you want to copy using the Shift or Ctrl key.

How to Copy Multiple Sheets in Excel

  • Now, select any sheet and drag it using the left side of the mouse key.
  • Then, hold the Ctrl key and place it where you want to copy the sheets.

How to Copy Multiple Sheets in Excel by Dragging

  • Finally, all selected sheets will be copied into the workbook.


1.2 To Another Workbook

Now, follow the steps given below to copy multiple sheets in Excel by dragging them to another workbook.

Steps:

  • First, select the multiple sheets you want to copy.

  • Then, select any sheet and using the left side of the mouse drag it to another workbook.
  • Now, hold the Ctrl key to copy the sheets.
  • While holding Ctrl place it at the end of the available sheets of the new workbook.

  • Here, we copied all the selected sheets at the end of Book1. You can see all the copied sheets in a new workbook.


2. Copy Multiple Sheets in Excel by Right-Clicking

You can also copy multiple Excel sheets by Right-clicking on the mouse.

2.1 Within Same Workbook

Here, you will find the way to copy an Excel sheet within the same workbook by Right-clicking on the mouse.

Steps:

  • In the beginning, Use Shift or Ctrl first to select multiple sheets.
  • Then, just click right on the mouse.
  • After that, select Move or Copy.

How to Copy Multiple Sheets in Excel by Right-Clicking

  • Now, a dialog box will pop up.
  • Next, you can select the position where you want to put copied sheets from the Before sheet.  Here, we selected to move to the end.
  • Then, mark on Create a copy.
  • Finally, click OK.

  • Thus, all the selected sheets are copied at the end of the previous sheets.


2.2 To Another Workbook

To copy multiple Excel Sheets by right-clicking to another workbook, follow the same steps shown above. Then, in the Move or Copy box follow the steps given below.

Steps:

  • From To book, you can select the workbook name and from the Before sheet, you select the place where you want to put copied sheets.
  • Here, we selected Book2 as To book.
  • Then, mark on Create a copy.
  • Finally, click OK.

How to Copy Multiple Sheets in Excel by Right-Clicking to Another Workbook

  • Finally, multiple selected sheets are copied at the end of sheet1 of Book2.


Conclusion

In this article, I explained the easiest ways of copying a sheet in Excel within the same and different workbooks. I hope this article will help you copy an Excel sheet easily. You are most welcome to give any kind of suggestions, feedback, or ideas. Feel free to comment down below. And visit ExcelDemy for many more articles like this. Thank you!


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo