Certain workbooks, like an attendance sheet, follow the same format in each sheet. When working with such workbooks, you may find the need to duplicate not just a specific data, but the entire worksheet or tab.
In Excel, you can duplicate the active worksheet or copy the data from the active worksheet while entering your data. In this article, we will go through each of these methods to duplicate a tab in Excel.
How to Duplicate a Tab in Excel
There are two approaches to creating duplicates of your current worksheet in Excel. Firstly, you can use the Ctrl key to duplicate the sheet, or you can also the Move or Copy window to create and place the worksheet your duplicated worksheet. You can also use this tool to copy the selected sheet to a different workbook.
Using the CTRL Key
Head on to the worksheet you wish to the worksheet you’re looking to duplicate. The worksheet you’re looking to duplicate must be your active worksheet.
On your keyboard, find and hold on to the Ctrl key. Then, drag your current worksheet from the workbook view. This will create a duplicate copy of your sheet. Drop the copied sheet anywhere in the worksheet view.
Use the Move or Copy Window
You can also duplicate your sheet using the Move or Copy window. Move or Copy allows you to copy a tab from the workbook view and place it within or in a different workbook. If you want to copy the sheet to a different workbook, make sure it’s currently open.
- From the worksheet view, right-click on the sheet you wish to duplicate.
- Select Move or Copy.
- Under To Book, select the destination workbook. You can leave this section as it is if you wish to duplicate the tab within the current workbook.
- Under Place Before, select the sheet you wish to place your duplicated tab in front of. You can also select (move to end) if you do not want to put it in front of any worksheet.
- Select the box next to Create a copy > OK.
Duplicate Data Before Entering it
If you’re yet to enter your data, you can select the sheets you wish to duplicate the value, and Excel will automatically copy the changes you’ve made in the active sheet to the selected sheets. This will save you time from entering, and then duplicating the tabs each time you wish to generate a copy of the worksheet.
- Create the worksheets from the worksheet view.
- While staying on the active sheet, hold down on the Ctrl key.
- Select the worksheet you wish to duplicate the value while holding on to the Ctrl key.
- After selecting the sheets, you can let go of the key and enter your data in the active sheet as usual.
- Once you’re done, right-click on your sheet from the view and select Ungroup Sheets.
Create a Macro to Duplicate Sheets
If you frequently find yourself in need of duplicating sheets, you can create a macro that duplicates a specific sheet for you. Then, you can assign a shortcut to the macro so that every time you hit the combination, the set worksheet will be duplicated.
Fortunately, you do not have to be a VBA expert to create such macros as you can record it within the program. Excel has the feature to Record Macros, using which you can perform the action you wish to generate the code.
In our context, after hitting the record macro button, we will be duplicating the first sheet in our workbook. Excel will record our mouse clicks and keystrokes and then generate a macro code. Make sure you have enabled the Developer tab then follow these steps to record and use a macro in Excel:
- Go to the Developer tab.
- From the Code section, select Record Macro.
- Give your Macro a name and a shortcut. Remember to use a unique shortcut or else the macro will override the current function of the shortcut.
- Enter the scope of the Macro.
- Use the second method in the above section to create a duplicate sheet.
- Select Stop Recording from the Code section in the Developer tab.
- Use the assigned shortcut to duplicate the first sheet.
