How to duplicate and rename sheets based on a list of names using VBA?

To duplicate and rename sheets in Excel based on a list of names using VBA, you can follow these steps. This macro assumes that you have a list of names in a worksheet, and you want to duplicate a specific template sheet for each of these names.

Here’s an example VBA macro to accomplish this:

Sub DuplicateAndRenameSheets()
    Dim wsTemplate As Worksheet
    Dim wsNew As Worksheet
    Dim nameRange As Range
    Dim cell As Range
    Dim shtName As String
    
    ' Set the sheet that contains the list of names
    ' Assume names are in Column A starting from A1
    Set nameRange = ThisWorkbook.Sheets("SheetNames").Range("A1:A10")
    
    ' Set the template sheet to duplicate
    Set wsTemplate = ThisWorkbook.Sheets("Template")
    
    On Error GoTo ErrorHandler
    
    ' Loop through each cell in the name range
    For Each cell In nameRange
        ' Check if the cell is not empty
        If cell.Value <> "" Then
            ' Create a new sheet by duplicating the template
            wsTemplate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Set wsNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            
            ' Rename the new sheet
            shtName = cell.Value
            wsNew.Name = shtName
        End If
    Next cell

    MsgBox "Sheets duplicated and renamed successfully."

    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

Open the VBA Editor:

Press `ALT` + `F11` to open the VBA editor.

Insert a Module:

Right-click on any item in the Project Explorer.
Select `Insert` > `Module`.

Paste the VBA Code:

Copy and paste the following code into the module window:

Customize the Code:

Replace `”SheetNames”` with the name of the sheet containing your list of names.
Adjust the range `Range(“A1:A10”)` to match the actual range of your names.
Replace `”Template”` with the name of the template sheet you intend to duplicate.

Run the Macro:

Make sure your Excel workbook is saved, as running macros can’t be undone.
Press `F5` while in the VBA editor to run the macro, or run it from the Excel Ribbon (`Developer` tab > `Macros` > select the macro and click `Run`).

This macro will duplicate a designated template sheet for every name listed in your specified range and rename each newly created sheet with the corresponding name from the list. Adjust the range and sheet names as necessary to suit your workbook’s layout.

facebooklinkedin