Macro to duplicate sheet as per lista nd final liste

Use a VBA macro that:

1. Reads names from Dash Board!C20:C... until the last used row in column C.
2. Copies Master_Template.
3. Renames each copied sheet to the value from column C.
4. Places the copied sheet after Master_Template, which keeps the created sheets between Master_Template and Input_Data.
5. Writes the created sheet name to column F starting at F20 and adds a hyperlink to that sheet.

Sub CopyAndRenameSheets()
    Dim wsDash As Worksheet
    Dim wsTemplate As Worksheet
    Dim wsNew As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim newName As String
    Dim outRow As Long
    
    Set wsDash = ThisWorkbook.Worksheets("Dash Board")
    Set wsTemplate = ThisWorkbook.Worksheets("Master_Template")
    
    lastRow = wsDash.Cells(wsDash.Rows.Count, "C").End(xlUp).Row
    outRow = 20
    
    For i = 20 To lastRow
        newName = Trim(wsDash.Cells(i, "C").Value)
        
        If newName <> "" Then
            wsTemplate.Copy After:=wsTemplate
            Set wsNew = ActiveSheet
            wsNew.Name = newName
            
            wsDash.Cells(outRow, "F").Value = newName
            wsDash.Hyperlinks.Add _
                Anchor:=wsDash.Cells(outRow, "F"), _
                Address:="", _
                SubAddress:="'" & newName & "'!A1", _
                TextToDisplay:=newName
            
            outRow = outRow + 1
        End If
    Next i
End Sub

If the goal is to keep the copied sheets in the same order as the list, this works because each new sheet is copied after Master_Template, so the newest copy is inserted directly after it.

If duplicate or invalid sheet names exist in column C, the rename step will fail. Sheet names must be valid and unique.

• Programmatically copy worksheets
• Sheets.Copy method (Excel)
• Worksheet.Copy method (Excel)
• hypelink the list name with the duplicated sheets in marco - Microsoft Q&A