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]

Hi & welcome to MrExcel.
How about

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))
        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]

Hi & welcome to MrExcel.
How about

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))
        If Not Evaluate("isref('" & c.Value & "'!A1)") Then
            sh1.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = c.Value
        End If
    Next
End Sub

Thanks Fluff! This code resolved the issue. But I am getting an error code at the end of it. (Please refer to the screenshot attached).

Also, I will assign this Macro with a Button on the sheet. So, I want that once all the client sheets are added (present in the list), it should display a message "All the clients are added".

Please Help!

 

[Fluff]

Which line of code gives the error?

 

[Utk_Gpt]

Which line of code gives the error?

If Not Evaluate("isref('" & c.Value & "'!A1)") Then

Please check the screenshot attached

 

[Fluff]

Do you have any blank cells in col B?

 

[Utk_Gpt]

Do you have any blank cells in col B?

No. There are no blank cells in column B. Column B is taking names of client from another worksheet. But there are no blanks in between.

 

[Fluff]

Ok, it looks as some of those names are greater than 31 characters, which is the max length of a sheet name.

 

[Utk_Gpt]

Ok, it looks as some of those names are greater than 31 characters, which is the max length of a sheet name.

All the characters are lower than or equal to 31. I have ensured this already

 

[Utk_Gpt]

Ok, it looks as some of those names are greater than 31 characters, which is the max length of a sheet name.

No item in coloumn B has more than 31 characters. How can the error be resolved and also how to display the message once all the client sheets are added? The display message is "All the clients are added".

Please help.