You may want to create copies of your worksheet for different reasons such as you may want to keep a backup, or you may want to do various calculations on the same dataset. In this article, we’ll describe 5 easy ways to copy an Excel sheet to another sheet.
Furthermore, for conducting the session, we will use Microsoft 365 version.
How to Copy Excel Sheet to Another Sheet: 5 Easy Ways
Here, we will describe 5 suitable methods for how to copy an Excel sheet to another sheet. In addition, for your better understanding, we’re going to consider the following sheet named “Dataset”. Now, we will copy this sheet to a new sheet.
1. Using Context Menu Bar to Copy a Sheet in Excel
Here, we can easily copy a sheet by using the Context menu bar.
- Select the sheet that you want to copy.
- Right-click on the sheet name.
- From the Context menu bar >> select Move or Copy.
As a result, a dialog box named Move or Copy will appear.
- Now, select the sheet before which you want to copy your sheet. Here, we have selected the sheet named Dataset.
- Then, check the box Create a Copy.
- After that, press OK.
At this time, you will see that your sheet will be copied before the sheet you selected (“Dataset”).
2. Employing Keyboard Shortcuts to Duplicate a Sheet in Excel
You can also manually copy your sheet to a new sheet. Basically, we are going to use some keyboard shortcuts to copy an Excel sheet to another sheet.
- Select the first cell on your sheet.
- To select all the occupied cells press CTRL + SHIFT + END.
- Subsequently, copy all the cells by pressing CTRL + C.
- Then, open a new worksheet in your workbook. Here, we have opened the worksheet named “Keyboard Shortcuts (2)”.
- Then, select the first cell.
- Consequently, press CTRL + V.
As a result, your sheet will be copied to the worksheet named “Keyboard Shortcuts (2)”.
3. Copying a Sheet to Another by Dragging
The easiest way to copy your sheet is by Dragging.
- First, press CTRL >> then click on the sheet which you want to copy. Or, you can click on the sheet which you want to copy and then press CTRL.
- Subsequently, drag the sheet to where you want to copy.
After that, you will get your copied sheet.
4. Use of Cells Tab to Copy a Sheet in Excel
Another way to copy a sheet is by using the Cells tab from the top Ribbon. Basically, we will use the Format feature.
- Open the sheet you want to copy.
- Go to the Home tab >> then from Cells >> select Format >> after that choose Move or Copy Sheet.
So, the dialog box named Move or Copy will appear.
- Then, select the sheet before which you want to copy your sheet.
- Subsequently, check the box Create a copy.
- Press OK.
After pressing OK your sheet will be copied before the sheet you selected.
5. Applying VBA Code to Copy a Sheet to Another Sheet in Excel
You can employ a VBA code to copy a sheet to another one in Excel. The steps are given below.
- You need to open your worksheet. Here, you must save the Excel file as an Excel macro-enabled workbook (xlsm).
- You have to choose the Developer tab >> then select Visual Basic.
- At this time, from the Insert tab >> you have to select Module.
- After that, write down the Code given below in Module 1.
Public Sub Copying_sheet_with_renaming() Dim copy_sheet As String copy_sheet = InputBox("Write duplicated worksheet name") If copy_sheet <> "" Then ActiveSheet.Copy After:=Worksheets(Sheets.Count) ActiveSheet.Name = copy_sheet End If End Sub
- Here, we have created a Public Sub Procedure named Copying_sheet_with_renaming.
- Next, we declare a variable copy_sheet as a String.
- Then, we used Input Box to rename the copied sheet.
- Now, save the code then go back to the Excel file.
- Then, from the Developer tab >> select Macros.
- At this time, select Macro (Copying_sheet_with_renaming) and click on Run.
Finally, you will see the following input box named Microsoft Excel.
- So, write a name for the copied one.
- Then, click on OK to see the sheet.
Finally, you will get your copied sheet named “Copy VBA Code”.
How to Copy a Sheet to Another Workbook in Excel
You can also copy sheets from one workbook to another. Suppose you want to copy this “Dataset” sheet to a new workbook.
- First, you need to open the Move or Copy box in your workbook by right-clicking on the sheet name.
- But, if you want to copy the worksheet to another workbook (which already exists) then you need to open the two workbooks at the same time, first >> after that, you need to open the Move or Copy box in your first workbook by right-clicking on the sheet name.
- After opening the Move or Copy dialog box >> select the new book in the To book box.
- Then, check the Create a copy box.
- Finally, press OK.
After that, your sheet will be copied into the “Book2” workbook.
Furthermore, you can also copy a sheet from another workbook by dragging or using the Format feature which is stated above.
Now, you can practice by yourself.
Download Practice Workbook
You can download the practice workbook from here:
By applying any of the five methods you will be able to copy one sheet to another sheet in Excel. Hope you find this article helpful. Feel free to leave a comment if you have any confusion.