How to create a new sheet with the same layout as another using VBA?

To create a new sheet in Excel with the same layout as an existing sheet using VBA, you can copy the existing sheet, which will duplicate its structure including formatting, column widths, row heights, and any existing formulas. Here’s a simple code snippet to do this:

Sub DuplicateSheetLayout()
    Dim ws As Worksheet
    Dim newSheet As Worksheet
    Dim sheetName As String

    ' Specify the name of the existing sheet you want to copy
    sheetName = "OriginalSheetName" ' Change this to your existing sheet name

    ' Check if the specified sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "Sheet '" & sheetName & "' does not exist."
        Exit Sub
    End If

    ' Copy the sheet to create a duplicate
    ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    ' Get the new sheet that was just created
    Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    ' Rename the new sheet if desired
    On Error Resume Next
    newSheet.Name = "NewSheetName" ' Change this to your desired new sheet name
    On Error GoTo 0

    If Err.Number <> 0 Then
        MsgBox "Could not rename the new sheet. A sheet with that name may already exist."
        Err.Clear
    End If

    MsgBox "New sheet with the same layout has been created."
End Sub

Instructions:

Specify the Original Sheet Name: Replace `”OriginalSheetName”` in the code with the name of the sheet you want to duplicate.
Specify the New Sheet Name: Replace `”NewSheetName”` with the desired name for the new sheet.
Run the Macro: Open the VBA editor (by pressing `ALT + F11`), insert a new module (by right-clicking on any existing module or the VBAProject and selecting `Insert -> Module`), then paste the code into the module.
Execute the Code: Run the macro by pressing `F5` while in the VBA editor to execute the code, or close the editor and run it from Excel’s macro menu (`ALT + F8`).

This will create a new sheet with the same layout as the specified original sheet. Make sure that the new sheet name doesn’t already exist to avoid errors when renaming.

facebooklinkedin