If you want to create a new worksheet, you don’t have to start afresh. You can duplicate an existing worksheet and make changes to it.
For example, let’s imagine you want to make an invoice. Since it is time-consuming to write the document from scratch, you can instead duplicate an existing invoice and fill the copy with new details. In this article, we will discuss five methods of duplicating worksheets in Excel.
5 Methods of duplicating worksheets in Microsoft Excel:
- Duplicating worksheets within a workbook using the Format option
- Duplicating a worksheet to a different workbook
- Duplicating worksheets by right-clicking the worksheet tab
- Duplicating worksheets using the CTRL key
- Duplicating worksheets using VBA Macros
Let us begin.
Method #1: Duplicate Worksheets within a Workbook Using the Format Option
This method will help you duplicate worksheets from the Home tab. Follow the steps below to reproduce worksheets using the Format option.
Step #1: Click Format
Click on the Home tab in the top menu bar to change the ribbon.
Click on the Format icon. You will get a drop-down menu.
Step #2: Move or Copy Sheet
Select Move or Copy Sheet… from the drop-down menu.
This action will open the Move or Copy dialog box.
Step #3: Create a copy
Ensure Excel highlights the name of your current worksheet.
Next, check the Create a copy tick box.
Finally, click the OK button.
This action will create a duplicate of the worksheet within your current workbook.
Method #2: Duplicate a Worksheet to a Different Workbook
This method is similar to Method 1 above. Follow the steps below to duplicate worksheets to other workbooks using this method.
Step #1: Click Format
Click on the Home tab in the top menu bar to change the ribbon.
Click on the Format icon. You will get a drop-down menu.
Step #2: Move or Copy Sheet
Select Move or Copy Sheet… from the drop-down menu.
This action will open the Move or Copy dialog box.
Step #3: Create a copy
Select the option (new book) from the To Book: list.
Next, tick Create a Copy and finally, click the OK button.
This action will duplicate the worksheet as a different workbook on your computer. You should open the duplicate workbook and save it.
Method #3: Duplicate Worksheets by Right-Clicking the Worksheet Tab
This is the fastest method for duplicating worksheets.
Step #1: Right-click on the worksheet name tab
You will find the worksheet tab on the lower part of your workbook. By default, it is named Sheet1. Right-click on this worksheet tab. This action will give you a context menu.
Step #2: Select Move or Copy
Click on Move or Copy… from the pop-up menu. This action will open the Move or Copy dialog box.
Step #3: Create a copy
Ensure Excel highlights the name of your current worksheet.
Check the Create a copy box and click OK.
Method #4: Duplicating Worksheets Using the CTRL Key
This method duplicates worksheets without using the menu options. Following the steps below will help you duplicate worksheets easily.
Step #1: Press and hold the CTRL key
On your keyboard, press the CTRL key and hold it in.
Step #2: Drag the worksheet
Click on the name tab of the worksheet you want to duplicate.
Drag it to the right and release your mouse pointer and the CTRL key.
Without opening any menu, this action will duplicate a worksheet.
Method #5: Duplicating Worksheets Using VBA Macros
This method can create multiple duplicates of a worksheet at once, but it is more complex than the other options. However, it can save you time when you need many duplicates of a worksheet. The steps below will let you create multiple duplicates of a worksheet at a time.
Step #1: Open Excel’s Visual Basic Editor
Press Alt + F11 on your keyboard if you use a PC and press fn + option + F11 on your keyboard if you are using a Mac.
Or
Go to the Developer tab and select the Visual Basic icon.
This action will open Excel’s Visual Basic Editor.
Step #2: Open the Macro editor
Click on the View tab of the Microsoft Visual Basic window.
A drop-down menu will open.
Select Immediate Window from the drop-down.
Step #3: Write the code
In our example below, we intend to make 12 duplicates of the worksheet. You should replace 12 with the number of duplicates you would like to make.
Type the following code into the text editor:
Do Until Sheets.Count=12: ActiveSheet.Copy,ActiveSheet: Loop
Next, press Enter.
Ensure the code is written in a single line. If the code is incomplete or incorrect, you will get error prompts. Protected worksheets can also trigger error prompts. If the code works correctly, it should create the specified number of worksheets.
In our example, the code works perfectly and duplicates Sheet1 in 12 places.
Conclusion
Duplicating worksheets is all about saving time. The methods given in this article will help you save time. Most methods here will work if you only need a few duplicates. However, the last method will work when you need many duplicates of a worksheet. Whatever the scale of duplication you need, you can create duplicate worksheets in no time using any of the above methods.