How to duplicate a tab in Microsoft Excel

By SpreadCheaters
Page last updated: 08/06/2023 | Next review date: 08/06/2025
close

Author bio


SpreadCheaters
View More north_east

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:
Sub DuplicateSheet()
    Dim sheetName As String
    sheetName = “Sheet1”
   
    If SheetExists(sheetName) Then
        Sheets(sheetName).Copy after:=Sheets(Sheets.Count)
    End If
End Sub

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
End Function

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.