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

Anyone may need the 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.

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

DataSheet to Practice

5 ways to Copy a Sheet in Excel

1. Copy a sheet in Excel by Dragging

I. Copy an Excel Sheet in Same Workbook

To copy an existing sheet in Excel by dragging, first, go to Sheet tabs and select the Sheet you want to copy then Hold the CTRL-key. While holding the key drag the Sheet using your left mouse-key or touchpad.

Copy a sheet 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 won’t copy

Copy a sheet by dragging

II. Copy an Excel Sheet in Another Workbook

To copy a sheet from one workbook to another by dragging, first, go to the Sheet tabsSelect the Sheet you want to copy then hold the CTRL-key.

Copy top another workbook

Now drag it by using the left mouse key to another workbook Sheet tab then place the icon there where you want to place the Sheet. Now you can see the same Sheet in a different workbook.

Copy top another workbook

Read more: How to Copy Excel Sheet to Another Sheet

2. By Right-Clicking on Mouse

  I.Within Same Workbook

To copy Excel Sheet by right-clicking first select the Sheet then click on the right side of the mouse. Now select Move or Copy.

Using Right click

After selecting Move or Copy a dialog box will pop up. From there from the Before sheet select the sheet you want to copy then mark on the Create a copy. Finally, click OK.

Copy using right click

Here, I have selected the Product sheet. After clicking on OK. You will find the copied sheet Product(2).

Copied sheet by right click

 II.To Another Workbook

To copy Excel Sheet by right-clicking first select the Sheet then click on the right side of the mouse. Now select Move or Copy.

copy to another book by right click

After selecting Move or Copy a dialog box will pop up. First, 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. Finally, click OK.

Copy to another workbook by right click

I selected the workbook Book1 and the sheet Product. After clicking on OK  will find the copied sheet Product to another workbook Book1.

Copy to another workbook

3. Copy a Sheet in Excel using the ribbon

I. Within Same Workbook

To copy a sheet using ribbon first select the sheet then go to the Home tab >> expand Cells>> Format >> Move or Copy Sheet.

Copy a sheet using ribbon

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

Copy a sheet using ribbon

I selected Sales so the copied sheet is placed before Sales as Sales(2).

Copying a sheet using ribbon

II.To Another Workbook

To copy a sheet in another workbook using ribbon, first, select the sheet the go to Home tab >> expand Cells>>click on Format >> select Move or Copy Sheet.

Copy a sheet using ribbon

After selecting Move or Copy Sheet a dialog box will pop up. 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.

Copy a sheet to another workbbok

I selected Book1(1) in To book and move to end in the Before sheet. The Copied sheet of Sales will be now placed in a new workbook Book1(1).

copied to another workbook


Similar Readings:


4. Copy an Excel sheet using VBA

First, select the sheet you want to copy. Open Developer tab>> Visual Basic.

Using VBA

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

VBA Editor

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("Person").Copy _

After:=ActiveWorkbook.Sheets("Person")

Next

End Sub

Here, I used the Sheet name Person to apply for a copy. You can use the sheet name of yours that you want to copy.

VBA Code

Now go back to the Excel Workbook. Go to View>> then click on Macros>> select View Macros

Using Macro to copy a sheet

After selecting View macros 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.

I selected my working workbook Copy a Sheet in Excel. After selecting now click on Run.

Macro dialog box

A new dialog box will pop up. Here it will show “ How many copies do you want? “ From there you can select the number of copies you want then click on OK.

Using macro to copy a sheet

I selected 2. Now it will show two copies of the Person sheet.

copied a sheet using macro

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

5. Copy a Sheet in Excel Web Version

In case anyone is using online Excel and wants to know how to copy a sheet there. First, select the sheet you want to copy then click on the right side of the mouse. Now select duplicate.

Web Excel

After selecting a duplicate it will copy the sheet before to your selected sheet.

copied a sheet in we excel

Copy Multiple Sheets in Excel

1. Copy Multiple Sheets in Excel by dragging

 I.Within Same Workbook

The method I 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.

To use CTRL while holding CTRL select the sheets you want to select it won’t select all sheets.

I selected all the sheets of my workbook.

Multiple sheet copy

Now select any sheet and drag it using the left side of the mouse key.

Multiple sheet copies within same workbook

After that, drag it to the end of the sheets.

Copies Multiple sheet

Now holding the CTRL key and place it where you want to copy the sheets. All selected sheets will be copied in the workbook.

Copied multiple sheets

II. To  Another Workbook by dragging

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.

Multiple sheet copy

Now hold the CTRL-key it will copy the sheets. While holding CTRL place it at the end of the available sheets of the new workbook. 

Copies Multiple sheet

I placed it at the end of Sheet1 of Book1(1).

Another workbook

I copied all the selected sheets at the end of Book1(1). You can see all the copied sheets in a new workbook.

copied to another workbook multiple sheets

2. Copy Multiple Sheets in Excel by right-clicking

I.Within Same Workbook

Using SHIFT or CTRL first select multiple sheets then just click right on the mouse. Then select Move or Copy.

copy multiple sheet by right click

After selecting Move or Copy a dialog box will pop up. Now you can select the position where you want to put copied sheets from the Before sheet. Then mark on Create a copy and finally click OK. I selected to move to the end.

copy multiple sheet by right click

Now all the selected sheets are copied at the end of the previous sheets.

Copied multiple sheets

II.To Another Workbook

After selecting multiple sheets click on the right side of the mouse. Then select Move or Copy.

copy multiple sheet by right click

After selecting Move or Copy a dialog box will pop up. 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.

I selected Book1(1 ) in To book and move to end in the Before sheet.

Copied Multiple sheets by right click

Multiple selected sheets are copied at the end of sheet1 of Book1(1).

copied to another workbook multiple sheets

Conclusion

In this article, I explained the easiest ways of copying a sheet in Excel within the same and different workbook. I hope this article will help you to copy a sheet in Excel easily. You are most welcome to give any kinds of suggestions, feedback, ideas. Feel free to comment down below.


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