How to duplicate a tab in Microsoft Excel
In Excel, duplicating tabs involves making a replica of an existing worksheet in the same workbook. This feature is crucial as it enables users to work efficiently with numerous worksheets that share the same layout, structure, or data, hence saving time and effort.
In this tutorial, we will learn how to duplicate a tab in Microsoft Excel. Duplicating tab in Excel is a common task in Excel that can be performed in multiple ways. We can use the context menu to duplicate a tab or we can also use a VBA code for this purpose.
Method 1: Duplicate a Tab by Utilizing the Context Menu
Step 1 – Perform a Right-click on the Tab
- Perform a right-click on the tab to duplicate.
Step 2 – Choose the Option Labeled as “Move or Copy”
- Choose the option labeled as “Move or Copy”
Step 3 – Check the Box with the “Create a Copy” Option
- Check the “Create a copy” checkbox at the bottom left corner of the dialog box.
Step 4 – Strike the OK Button
- Strike the OK button in the dialog box.
- The tab will be duplicated.
Method 2: Duplicate a Tab by a VBA Code
Step 1 – Locate the Visual Basic Button
- Locate the Developer tab.
- Press the button labeled “Visual Basic” situated on the left-hand side of the ribbon.
Step 2 – Create a Fresh Module
- Perform a Right-click on the sheet name in the Project-VBA Project menu at the left of the window.
- Click on Insert.
- Click on Module
Step 3 – Enter the VBA Code
- Enter the following VBA code:
Dim sheetName As String
sheetName = “Sheet1”
If SheetExists(sheetName) Then
Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Sheets(sheetName)
SheetExists = Not ws Is Nothing
Step 4 – Run the Code
- Run the code by clicking on the Run button in the menu bar.
- Click on the Run/Sub user form option.