How To Create A Duplicate Sheet In Excel

When working in Excel, you'll sometimes need to create one or more copies of your spreadsheet. Fortunately, creating duplicate spreadsheets is not a difficult task.

In this article, you'll learn how to duplicate an Excel sheet in multiple ways and on different platforms. Also, we will cover other useful features related to sheet duplication, like moving a sheet, copying multiple sheets, unhiding sheets, and more.

How to Duplicate a Sheet in Excel?

The quickest way to duplicate a sheet is by dragging and dropping. Here's how it's done:

1. At the bottom of the screen, select the sheet tab you want to copy.
   
2. Hold the "Control" key (Ctrl) on your keyboard.
   
3. While holding the Ctrl key, drag and drop the tab with your mouse.
   

If you have too many sheets and want the copy of your sheet to appear at a specific location, do the following:

1. Right-click on the sheet tab you want to copy.
   
2. Click "Move or Copy."
   
3. Choose the workbook in which you want to place your duplicate.
   
4. Select the sheet before which you want your duplicate to appear.
5. Check "Create a copy."
   
6. Click "OK."
   

How to Duplicate a Sheet in Excel on Mac?

For Mac users, the dragging technique is applicable as well:

1. Press and hold the "Option" key.
   
2. Click on the sheet tab and drag it where you want.
   
3. Drop the sheet tab and release the Option key.

However, if you want to copy your sheet to another workbook, the method is slightly different:

1. Open the workbook in which you want to place your duplicate.
2. In the workbook that contains the original, right-click on the sheet tab.
   
3. Click on "Move or Copy."
   
4. Choose the workbook where you want to paste your sheet.
   
5. Make sure that you check "Create a copy."
   
6. Click "OK."
   

How to Duplicate a Sheet in Excel Multiple Times?

You can use the drag-and-drop technique to quickly multiply the number of your Excel sheet duplicates:

1. Create a copy of your sheet using one of the methods above.
2. Hold Shift and select the tabs of the copied sheet and the original using your mouse. Note that the two sheet tabs have to be next to each other.
3. Release Shift and hold Ctrl.
4. Drag and drop the two tabs.
   
5. Release Ctrl.
   

Repeat these steps. Each time, you can increase the number of identical sheet tabs you want to duplicate further.

How to Duplicate a Sheet in Excel With a Shortcut?

If you want to create a larger number of sheet copies with the click of a button, you can create a keyboard shortcut:

1. Go to the "View" tab in the Ribbon.
   
2. Click on "Macros."
   
3. In the drop-down menu, click "Record Macro."
   
4. Enter the shortcut key you want to use (e.g. "D").
   
5. Click "OK."
   
6. Right-click on the sheet tab you want to duplicate.
7. Click "Move or Copy."
   
8. Select the workbook in which you want to paste your copy.
9. Choose the sheet before which you want your duplicate to appear.
   
10. Check "Create a copy."
    
11. Click on "Macros" again.
    
12. Select "Stop recording."
    

Now, click on the sheet tab and press Ctrl + D to instantly duplicate the sheet. Use this shortcut as many times as you need.

How to Duplicate a Sheet in Excel in VBA?

Microsoft Office programs have their own programming language – Visual Basics for Application (VBA). With this, you can program Excel to make a copy of a sheet for you.

First, you will need to open VBA:

1. If you don't see the "Developers" tab on the Ribbon, go to "File."
   
2. Select "Options."
   
3. In the "Customize Ribbon" section, check "Developers."
   
4. Go back to your worksheet and open the "Developers" tab in the Ribbon.
   
5. Click on "Visual Basic."
   

Note:  You can use a shortcut (Alt + F11) to open VBA, but this may not work for all users.

Now that you have VBA opened, you can create a code that will automate the duplication process:

1. Click on "Visual Basic" to open VBA.
   
2. Click the "Insert" tab and then "Module."
   
3. Copy and paste the following code:
   Sub Copier ()
Dim x As Integer
x = InputBox("How many copies do you want?")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub
   
4. Instead of Sheet 1, enter the name of the sheet you want to copy.
5. Return to your worksheet and click "View" on the Ribbon.
   
6. Click on the "Macros" tab and then "View macro."
   
7. Select the "Copier" macro and click "Run."
   
8. Enter the number of copies that you want to make (e.g. "20").
   
9. Click "OK."
   

How do I Duplicate a Sheet in Excel Online?

If you are using Excel online, there is also an easy way to duplicate a sheet:

1. Right-click on the sheet tab you want to duplicate.
2. Click "Duplicate."
   

How to Duplicate a Workbook in Excel?

First, you have to go to the dialog box that lets you open an existing Excel document. Accessing this dialog box varies depending on your version of Excel:

1. Excel 2007 – Office>Open
   Excel 2010 – File>Open
   Excel 2013 – File>Computer>Browse
   Excel 2016 – File>Browse
   
2. Navigate to the Excel document that you want to copy and select it.
3. Click the small arrow on the "Open" button.
   
4. From the drop-down menu, select "Open as Copy."
   

You now have two identical workbooks. Rename the copy of the new workbook if needed.

How to Move a Sheet in Excel?

There are two easy ways to move a sheet in Excel.

You can simply select the sheet tab you want to move and drag it to the desired location.

Or, if you have too many sheets, you can:

1. Right-click on the sheet tab you want to move.
2. Click "Move or Copy."
   
3. Select the sheet tab before which you want your sheet to appear.
   
4. Click "OK."
   

How to Move a Sheet in Excel With a Shortcut?

To create a shortcut for moving a sheet in Excel, you will need to create a Macro:

1. Go to the "View" tab on the Ribbon.
   
2. Click on the "Macros" tab.
   
3. Select "Record Macro."
   
4. Insert the key you want to use as a shortcut (e.g. "M").
   
5. Right-click on the sheet tab you want to move.
   
6. Select "Move or Copy."
   
7. Choose where you want to move your sheet.
   
8. Click "OK."
   
9. Go back to "Macros."
   
10. Click "Stop recording."
    

Each time you click Ctrl + M, Excel will move your sheet to the location you selected.

How to Copy Multiple Sheets Multiple Times in Excel?

The quickest way to copy multiple sheets is to:

1. Select the sheet tabs you want to copy while holding Ctrl.
   
2. Right-click on any of the selected sheet tabs.
3. Select "Move or Copy."
   
4. Click on the sheet before which you want the copies to appear.
   
5. Check "Create a copy."
   
6. Click "OK."
   

Repeat this process until you have the desired number of copies.

Additional FAQs

How Do I Unhide Sheets in Excel?

There may be some hidden sheets in an Excel file that you didn't create. You can check that easily and unhide any hidden sheets:

1. Right-click on any sheet tab.

2. Click "Unhide."

3. Select the sheet that you want to unhide and click "OK."

Unfortunately, you can't unhide all sheets at once. You will have to repeat this process for each hidden sheet.

Also, if there are no hidden sheets in the Excel file, the "Unhide" button will not be clickable.

How Do I Copy Sheet and Rename Automatically in Excel?

You will first have to open VBA in Excel and create a new Module:

1. Go to the "Developers" tab on the Ribbon.

2. Click on "Visual Basic."

3. Click "Insert" and then "Module."

4. Copy and paste the following code:

Sub Create()

'Updateby Extendoffice

    Dim I As Long

    Dim xNumber As Integer

    Dim xName As String

    Dim xActiveSheet As Worksheet

    On Error Resume Next

    Application.ScreenUpdating = False

    Set xActiveSheet = ActiveSheet

    xNumber = InputBox("How many copies do you want?")

    For I = 1 To xNumber

        xName = ActiveSheet.Name

        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)

        ActiveSheet.Name = "NewName" & I

    Next

    xActiveSheet.Activate

    Application.ScreenUpdating = True

End Sub

5. Instead of "NewName", enter the desired name of your copy. If you create multiple copies, Excel will assign suffixes ("-1", "-2", "-3" etc.) to each copy.

6. Select the sheet tab you want to make renamed copies of.

7. Go to the "View" tab on the Ribbon.

8. Click on "Macros" and then "View Macros."

9. Select the "Create" macro and click "Run.".

10. Enter the number of copies that you need (e.g. "5").

11. Click "OK."

Note: Pressing F5 on the keyboard can substitute steps 7. and 8, but this may not work for all users.

Now you have five renamed copies of your original sheet (i.e. "NewName-1", "NewName-2" …)

Creating a Duplicate Sheet in Excel

Whether you own a Mac, a PC, or work in Excel Online, you now know that creating duplicate spreadsheets is a simple task. We have even equipped you with the codes you need to copy and paste into your MS Excel.

Have you ever had any problems duplicating a sheet in Excel? How did you resolve the issue? Let us know in the comments section below.

How To Create A Duplicate Sheet In Excel

Source: https://www.alphr.com/duplicate-sheet-excel/

Posted by: welchnotheeptist.blogspot.com

Share this post