Create macro to duplicate the sheets, make liste and hyperlink-

Use a VBA macro that copies the template sheet with Copy After:=..., names each new sheet from Data File!C4:C15, places each copy immediately after the previous one, and writes a hyperlinked list in Data File starting at F4.

Sub CopyAndListSheets()
    Dim wsData As Worksheet
    Dim wsTemplate As Worksheet
    Dim wsNew As Worksheet
    Dim copyAfter As Worksheet
    Dim cell As Range
    Dim outRow As Long
    Dim newName As String
    
    Set wsData = ThisWorkbook.Worksheets("Data File")
    Set wsTemplate = ThisWorkbook.Worksheets("Master Template_Collectives")
    Set copyAfter = wsTemplate
    
    outRow = 4
    
    For Each cell In wsData.Range("C4:C15")
        If Trim(cell.Value) <> "" Then
            newName = CStr(cell.Value)
            
            wsTemplate.Copy After:=copyAfter
            Set wsNew = ActiveSheet
            wsNew.Name = newName
            Set copyAfter = wsNew
            
            wsData.Cells(outRow, "F").Value = newName
            wsData.Hyperlinks.Add _
                Anchor:=wsData.Cells(outRow, "F"), _
                Address:="", _
                SubAddress:="'" & newName & "'!A1", _
                TextToDisplay:=newName
            
            outRow = outRow + 1
        End If
    Next cell
End Sub

What this does:

1. Reads sheet names from Data File cells C4:C15.
2. Copies Master Template_Collectives once for each nonblank cell.
3. Places each new sheet after the previous copied sheet, so the order matches the list.
4. Leaves existing sheets untouched.
5. Writes the new sheet names to Data File!F4:F....
6. Creates hyperlinks in column F to each new sheet.

Notes:

• If a name in C4:C15 already exists as a worksheet name, renaming the copied sheet will fail.
• If a name is invalid for Excel sheet names, renaming will fail.
• This macro does not delete any existing sheets.

The worksheet copy behavior is based on Excel VBA Copy After:=..., which places the copied sheet after the specified sheet.

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