Copy template worksheet and rename it only if name already is not taken

[Utk_Gpt]

Hi All,
I am trying to find a macro that can copy a template worksheet multiple times and rename the worksheet based on a list in another worksheet starting from cell B6.
"Template" is the name of the sheet to be copied
"Client List" is the name of the worksheet in which the list is with some other information but I need to rename them according to the cell B6 and down.

I found a code in one of the threads relevant to this:

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Client List")
    For Each c In sh2.Range("B5", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    Next

Issue in the above code: I have to regularly update the "Client List" time to time thus require to re-run the macro. If I re-run the above code, it shows Error 1004 (Name is already taken) and creates a copy of "Template" with the name "Template (2)" at the end.

Modification required: If a sheet with the name is already created (as the macro was run earlier), the macro should continue to run and copy the "Template" sheet and rename it to the remaning values in the "Client List" till the list is not over. It should not affect the already created copies.

Please Help. Thank You!

 

[Fluff]

Ok, I was going by your code & missed the fact that the first name is in B6 not B5, try

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("sheet1")
Set sh2 = Sheets("Lists")
    For Each c In sh2.Range("B6", sh2.Cells(Rows.Count, 2).End(xlUp))
        If Not Evaluate("isref('" & c.Value & "'!A1)") Then
            sh1.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = c.Value
        End If
    Next
End Sub

 

[Utk_Gpt]

Sub AddClientSheet()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Client List")
    For Each c In sh2.Range("B6", sh2.Cells(Rows.Count, 2).End(xlUp))
        If Not Evaluate("isref('" & c.Value & "'!A1)") Then
            sh1.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = c.Value
        End If
    Next
End Sub

I am using this code and the error is same: Run-time Error '13'

Kindly help

 

[Fluff]

If you add the message box as shown, what does it return?

Set sh2 = Sheets("Client List")
MsgBox sh2.Cells(Rows.Count, 2).End(xlUp).Row
    For Each c In sh2.Range("B6", sh2.Cells(Rows.Count, 2).End(xlUp))

 

[Utk_Gpt]

Thank you so much for your constant efforts to resolve the issue.

It gave a message box "105". What I can understand, till 105 cell I have applied a formula to retrieve client list from another sheet. From 106 cell, there is no formula applied. According to the formula, if client name is not there on the Main sheet it will give "" on Client List. Please refer to the screenshot attached and let me know how it can be resolved.

 

[Fluff]

In that case you have "blank" cells in the range which is the problem.Try

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Client List")
    For Each c In sh2.Range("B6", sh2.Cells(Rows.Count, 2).End(xlUp))
        If c.Value <> "" Then
            If Not Evaluate("isref('" & c.Value & "'!A1)") Then
                sh1.Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = c.Value
            End If
         End If
    Next
End Sub

 

[Utk_Gpt]

In that case you have "blank" cells in the range which is the problem.Try

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Client List")
    For Each c In sh2.Range("B6", sh2.Cells(Rows.Count, 2).End(xlUp))
        If c.Value <> "" Then
            If Not Evaluate("isref('" & c.Value & "'!A1)") Then
                sh1.Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = c.Value
            End If
         End If
    Next
End Sub

Thank you so much Fluff!

The error is completely resolved now.
Just wish to know, how to add a message box saying "All clients are added" once all the client sheets in the list are added.

 

[Fluff]

Just add this before the end sub line

MsgBox "All clients are added"