We could be copying multiple formats from one sheet to another for the ease of filling data in for daily, weekly, or monthly reports. We could be copying data for adding in the analytics. We could very well be copying a to-do list to forever keep score. For whichever act it is that you choose to team up with Excel, we are here again to give you the detailed how-to.
This tutorial will cover copying a sheet in Excel to the same workbook using a few methods with a few simple clicks. You will also learn how to duplicate a sheet into multiple sheets and also how to add a duplicate sheet from one workbook to another workbook.
Sounds like what you’re looking for? Then let’s not wait to get started.
Method #1 – Using Excel Ribbon
For this option, we simply go with a few clicks using the humble and powerful ribbon. Here’s what we do:
- In the Home tab, from the Cells group, select the Format command button.
- From the Format drop-down menu, select the Move or Copy Sheet… Now the Move or Copy dialog window will launch.
- In the Before Sheet field, select the position you want the copied sheet to be; the copied sheet will be placed prior to the sheet selected in the Before Sheet. Let’s say we want the copied sheet to be between Sheet1 and Sheet2 so we’ll select Sheet2.
- Check the Create a copy. If this checkbox is not checked, the sheet will only be moved, not copied.
- Click OK.
And here is our newly copied sheet; copied and positioned before Sheet2.
Method #2 – Using Right Click Context Menu
This method is a near copy of the previous one with the same amount of clicks and also utilizes the Move or Copy dialog box. The steps are below:
- Right-click on the sheet tab that you want to copy. We will right-click Sheet1 as an example.
- From the right-click context menu, select the Move or Copy Sheet…Now the Move or Copy dialog window will launch.
- In the Before Sheet field, select the position you want the copy to be; the copied sheet will be placed prior to the sheet selected in the Before Sheet
Let’s say we want the copied sheet to be between Sheet1 and Sheet2 so we’ll select Sheet2.
- Check the Create a copy. If this checkbox is not checked, the sheet will only be moved, not copied.
- Click OK.
That’s our newly copied sheet; copied and positioned before Sheet2.
Recommended Reading – How to Lock and Protect Worksheets
Method #3 – Using Ctrl + Mouse Drag
This is probably the easiest method of all. One key and one-click (and a drag) – done! Find out how right below:
- Click on the sheet to be copied from the sheet tabs below. The sheet that you want to copy has to be the active sheet for this option to work. E.g., will go to Sheet1.
- Hold down the Ctrl key, then hold down the click on Sheet1 and drag the sheet to where you want to copy it. We will drag it to be copied before Sheet2.
- Release the click and then the Ctrl As soon as the click is released the sheet will be copied.
While dragging, a sheet icon with a plus sign will appear with the cursor. This indicates that the dragged sheet will be copied. If you let go of the Ctrl key while dragging, the plus sign vanishes from the sheet icon indicating that the dragged sheet will only be moved, not copied. You can hold down the Ctrl key again to see the plus sign reappear.
Using this option, you cannot position the copied sheet to be before Sheet1, given that Sheet1 is the sheet you are copying. Basically, the first sheet cannot be copied and placed before the first sheet. If you want a duplicate Sheet1 to be positioned before Sheet1, you will have to use the other options of copying the sheet.
Method #4 – Using VBA
For the coding junkies, we also have the VBA option. VBA (Visual Basic for Applications) can be used in MS Office applications to complete tasks using codes. We will show you how to use VBA for duplicating a sheet in Excel.
For this option, we need the VBA editor which is easily accessible using the keyboard shortcut Alt + F11. However, you can also access it from the Developer tab and if you don’t have the Developer tab, you can add it to the ribbon from Excel options.
Follow these steps to copy a sheet using VBA:
- Press Alt + F11 to launch the VBA editor.
- Click on the Insert tab to open its menu.
- Select Module from the menu.
- Copy the following code and paste it in the Module window:
Sub DuplicateActiveWorksheet()
Dim copies As Integer
copies = InputBox("How many copies do you want?")
For i = 1 To copies
ActiveWorkbook.ActiveSheet.Copy After:=ActiveWorkbook.ActiveSheet
Next
End Sub
- Close the Module window and the VBA editor.
- Back to the Excel worksheet that you wish to duplicate, go to the View tab, select the Macros button, and then the View Macros. The Macros window will now open.
- Select the relevant macro and click Run to execute the code.
- Now, if you noticed in the code, we have a "How many copies do you want?" input box coded in. A little dialog box will appear when you run the macro and you can enter the number of copies of the active sheet. We’ll go with 2 copies for now.
- Enter the number of copies in the popped-up dialog box and click OK.
Ready they are!
Copying one or two sheets is easier with other options mentioned above but for more than that, using VBA would be a better idea especially if duplicating sheets is a continual process for you. You can visit the macros list any time and run the command of duplicating sheets once you’ve initially fed the code in the VBA editor.
Duplicating Sheets from Another Workbook
There are three things you can do here:
- Copy a sheet from the current workbook to another workbook.
- Copy a sheet from another workbook into the current workbook.
- Copy a sheet from the current workbook to a new workbook.
Let’s show you the steps for option number 3 here so it’ll be easy to understand the first two.
- Open the workbook which has the sheet you want to copy to a new workbook.
- Right-click the sheet to be copied and select the Move or Copy … option from the right-click menu. This will open the Move or Copy dialog box.
- In the To book bar, click on the arrow to open the drop-down list. Here you will see the names of any open workbooks or a new book Click on new book.
- Check the Create a copy checkbox and click OK.
And this is luckily one of those many times that Excel does what we want it to do. Above, Sheet1 has been duplicated to a new workbook.
If you want to copy the sheet to another workbook (not a new workbook), make sure the recipient workbook is also open. Follow the same steps as above but choose the name of the recipient workbook instead of choosing new book in the dialog box. Follow the rest of the steps again to copy the sheet to another workbook. That should do it!
We’ll come to a close now. Dear readers and Excel-ers, that was all about copying and duplicating a sheet in Excel. We would be glad to have given you some insight and new ideas on something simple and regular as copying sheets. There will always be more to pick up on and we will be ready with something new for you. See you with another sheet!
About Mehwish Javaid