VBA to copy template sheet and rename based on items in list on master sheet

[KjKLady]

Help? I'm trying to set a macro that will reference a list within a "SiteMaster" worksheet (starting from C6) and create a new copy of a "LibraryTemplate" worksheet for each item in that list, then rename it to match how it is identified in the list. I also should note that the "SiteMaster" sheet will be named with the following pattern: "SiteMaster - Actual Site Name" - so I believe the reference to this master sheet will require a Like statement or wildcard function?

**Bonus ask - would also like this to check if a sheet name already exists, if it does, skip and do not copy/create a new worksheet.

 

[DanteAmor]

Hi @KjKLady, welcome to the board!

Try this

Sub copy_template()
  Dim sh As Worksheet, sh1 As Worksheet, c As Range
  Application.ScreenUpdating = False
  For Each sh In Sheets
    If sh.Name Like "SiteMaster*" Then
      Set sh1 = sh
      Exit For
    End If
  Next
 
  If sh1 Is Nothing Then
    MsgBox "The sheet does not exist: SiteMaster"
    Exit Sub
  End If
 
  For Each c In sh1.Range("C6", sh1.Range("C" & Rows.Count).End(xlUp))
    If Not Evaluate("ISREF('" & c.Value & "'!A1)") Then
      Sheets("LibraryTemplate").Copy after:=Sheets(Sheets.Count)
      ActiveSheet.Name = c.Value
    End If
  Next
 
  MsgBox "Done"
End Sub