How to duplicate a sheet and rename it using VBA?

To duplicate a sheet and rename it using VBA in Excel, you can use the following steps. This example assumes you want to duplicate an existing worksheet and then rename the duplicated sheet to a new name.

Here’s a simple VBA code snippet to achieve that:

Sub DuplicateAndRenameSheet()
    Dim originalSheet As Worksheet
    Dim newSheet As Worksheet
    Dim originalName As String
    Dim newName As String

    ' Set the original sheet you want to duplicate
    originalName = "Sheet1" ' Replace with your original sheet name
    newName = "DuplicatedSheet" ' Replace with your desired new sheet name

    ' Error handling in case the sheet does not exist
    On Error Resume Next
    Set originalSheet = ThisWorkbook.Sheets(originalName)
    
    If originalSheet Is Nothing Then
        MsgBox "The sheet named '" & originalName & "' does not exist.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0

    ' Duplicate the original sheet
    originalSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    ' Get the newly created sheet (it will be the last sheet)
    Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    ' Rename the duplicated sheet
    On Error Resume Next
    newSheet.Name = newName
    If Err.Number <> 0 Then
        MsgBox "A sheet named '" & newName & "' already exists.", vbExclamation
        Err.Clear
    End If
    On Error GoTo 0

End Sub

Open Excel and press `ALT + F11` to open the VBA editor.
Go to `Insert > Module` to create a new module, if you don’t already have one.
Copy and paste the following VBA code into the module:
Press `F5` or run the `DuplicateAndRenameSheet` macro to execute it.

Explanation:

originalName: Change it to the name of the sheet you want to duplicate.
newName: Change it to your desired name for the duplicated sheet.
The code includes basic error handling to check if the original sheet exists and if a sheet with the new name already exists.
The copied sheet is placed at the end of the workbook and renamed.

You can adjust the names and incorporate additional error checking as needed for your application.

facebooklinkedin