3 Easy Ways to Duplicate Sheet in Excel (Shortcuts + VBA)

by

Working with spreadsheets often involves a lot of repetitive tasks.

A common way that spreadsheet users save time and energy is by making copies of spreadsheets already created and using those after making the necessary edits.

In this way, you get to keep the structure and format of the original sheet, while only changing the things that need to be changed.

With Excel, making duplicates of sheets is very simple and quick. Moreover, there are multiple ways in which you can duplicate sheets. In this tutorial, we will see 3 quick ways to duplicate a sheet in Excel.

We will also see how you can make duplicates of multiple sheets as well as how you can make multiple duplicates of a single sheet.

3 ways to Duplicate One or Multiple Sheets in Excel

First, let us look at how you can make duplicates of one or more sheets in Excel.

There are three ways to do this.

Let us look at each of these methods one by one. For each of the methods, we will use the set of worksheets shown below:

Three sheets in a workbook

Using the Format Menu to Duplicate a Sheet in Excel

Let’s say “Sheet 1” is the currently active sheet. To make a duplicate of the sheet, follow the steps given below:

  1. Select the Home tab.
  2. Click on the Format button (under the Cells group).Click on the Format option
  3. From the drop-down menu that appears, select the ‘Move or Copy Sheet’ option.select the ‘Move or Copy Sheet’ option
  4. This will open the Move or Copy dialog box.Move or Copy dialog box
  5. Make sure the checkbox next to Create a Copy’ is checked.Check the Create a copy option
  6. Select where you want the duplicate sheet to go. The options you have are:
    1. In a different workbook – For this, select the dropdown list below ‘To book:’ and select the workbook you want to copy the sheet to. If you want the duplicate sheet to go into a new workbook, select the ‘new book’ option.select the ‘new book’ option
    2. Before a particular sheet’s tab – For this, from the list below ‘Before sheet:’, select the sheet before which you want the duplicate sheet to go.Select the before sheet option
    3. At the end of the workbook’s sheet tabs – For this, from the list below ‘Before sheet:’ select the ‘move to end’ option.Select Move to end option
  7. Click OK to close the dialog box.

Note: In the Move or Copy dialog box, the drop-down list below ‘To book’ displays only those workbooks that are currently open.

Using this Method to Duplicate Multiple Sheets

If you want to duplicate multiple sheets, press down the CTRL key and select the sheets you want to copy.

If the sheet tabs are next to each other, you can click on the tab of the first sheet, press down the SHIFT key, and select the last sheet that you want to duplicate.

After that, follow steps 1 to 7 shown above.

Using the Worksheet tab Context Menu to Duplicate a Sheet in Excel

You can also use Excel’s context menu to duplicate one or more sheets. To use this method, follow the steps below:

  1. Right-click on the tab of the worksheet that you want to duplicate.
  2. Select ‘Move or Copy’ from the context menu that appears.Select the Move or copy option
  3. This will open the Move or Copy dialog box.Move or Copy dialog box
  4. Make sure the checkbox next to ‘Create a Copy’ is checked.
  5. Select where you want the duplicate sheet to go.
  6. Click OK to close the dialog box.
  7. You should now see a duplicate of your selected worksheet created in your selected location.

Using this Method to Duplicate Multiple Sheets

If you want to duplicate multiple sheets, press down the CTRL key and select the sheets you want to copy.

After that, follow steps 1 to 7 shown above.

Dragging to Duplicate a Sheet in Excel

This method is by far the quickest. It lets you duplicate one or more worksheets without having to involve any menus. Here are the steps:

  1. Select the tab of the worksheet you want to duplicate.
  2. Hold down the CTRL of your keyboard and use the left mouse button to drag the tab to the right or left, depending on where you want the new duplicate sheet to go.Press control and drag
  3. You should see a small arrow at the top of the tabs indicating the position where the newly duplicated sheet will be placed.Arrow showing the position of the copied sheet
  4. Release the mouse button once you find the spot where you want the new sheet to go.Release the mouse button

You should now see a duplicate of your selected sheet at the point where you released your mouse button.

Duplicate sheet in inserted

Using this Method to Duplicate Multiple Sheets

If you want to duplicate multiple sheets, you can select the sheets you want to duplicate, and then follow steps 2 to 4.

Make sure you release the CTRL or Shift key before pressing down the CTRL key again for step 2.

Also read: How to Download One Sheet From Excel?

Making Multiple Duplicates of a Sheet (Using VBA)

There might be some special situations where you would want to make multiple duplicates of a sheet.

For example, you might need to make 20 copies of a worksheet to say, track transactions in different locations.

In such cases, you can use macros coded in VB Script to quickly make as many duplicates as you need to.

If you are apprehensive at the thought of coding, you really don’t need to be. Here’s a step-by-step on how to create a macro that can make multiple duplicates of a sheet.

The code that we will be using is given below:

Sub DuplicateSheet()
Dim x As Integer
x = InputBox("Enter number of times to copy the Active Sheet")
For numtimes = 1 To x
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
Next
End Sub

This code lets the user enter how many copies of the active sheet they want to make. It then creates that many copies and places the new worksheets at the end of the worksheet tabs list.

To create a macro using the above code to make multiple duplicates of a sheet, follow the steps below:

  1. Make sure the sheet you want to duplicate is the active sheet.
  2. From the Developer menu, select Visual Basic.Click on the green play button
  3. Once your VBA window opens, Click Insert->Module. Now you can start coding.Insert a module
  4. Type or copy-paste the code is shown above into the module window.Copy paste the code in the code window
  5. Run the code by clicking on the Run button from the toolbar on top.Click on the green play button
  6. You should see a message box asking you to enter how many copies you need to make.Message box asking for number of duplicate sheets
  7. Enter the number of duplicates you need and press the return key.Enter the number in the msgbox
  8. You should now see your required number of duplicates created towards the end of the workbook’s sheet tabs.Duplicate sheets inserted

Note: If you can’t see the Developer menu, from the File menu, go to Options. Select Customize Ribbon and check the Developer option from the Main Tabs. Finally, Click OK.

Once your code is ready and working, you can use it as many times as you need to. All you need to do is select ‘Macros’ from the Developer tab.

Click on Macros

Select the name of the macro (‘DuplicateSheet’).

Select the macro

Finally, click the ‘Run’ button to run the macro.

Click the Run option

In this tutorial, we showed you three ways in which you can create duplicates of one or more worksheets in Excel.

We also showed you how you can use VB Script to make multiple copies of a worksheet.

We hope our instructions were clear and that you found the tutorial helpful.

Other Excel tutorials you may like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment