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.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project