This does option 2; put yes in column L.
![]()
Sub DuplicateTEMPLATEandRenamewithName() Dim i As Integer, j As Long Dim ws As Worksheet Dim sh As Worksheet Dim wss As Worksheet Dim strName As String 'Dim sht As Worksheet 'Dim Btn As Excel.Button Set ws = Sheets("TEMPLATE") Set sh = Sheets("LIST") Application.ScreenUpdating = False Application.DisplayAlerts = False Application.CutCopyMode = False For i = 2 To sh.Range("H" & Rows.Count).End(xlUp).Row 'Col H is Names in LIST Sheet If LCase(sh.Range("L" & i).Value) = "yes" Then ws.Copy After:=sh ' Use Before if you want sheets to be produced strName = sh.Range("H" & i).Value & " " & sh.Range("K" & i).Value 'Col H is Names in LIST Sheet If Not Evaluate("ISREF('" & strName & "'!A1)") Then 'Test if worksheet name exists ActiveSheet.Name = strName Else j = 2 Do DoEvents If Not Evaluate("ISREF('" & strName & "(" & j & ")'!A1)") Then ActiveSheet.Name = strName & "(" & j & ")" Exit Do End If j = j + 1 Loop End If ActiveSheet.Range("B2").Value = sh.Range("B" & i).Value 'B2 is cell in Duplicated Sheet 'To Place the cursor at A1 Application.GoTo Reference:=ActiveSheet.Range("A1"), _ Scroll:=True End If Next i 'this loops Application.ScreenUpdating = True Application.DisplayAlerts = True Application.CutCopyMode = False End Sub
Bookmarks