Macro to copy a sheet and rename it but first check that names not been used before

[tonywatsonhelp]

Hi Everyone,

I would like a macro that will copy sheets "Master" (to the end is fine).
and rename it the name in Sheets "Welcome" O19

but before you copy the sheet i'd like to check that the name in Sheets "Welcome" O19 hasn't already been used as a tab name,
if it has cancel and messagebox " That name has already been used!"

if the copy goes ahead then
then copy the data in Sheets "Welcome" O19:O21 to copied sheet T4:T6

please help if you can thanks

Tony

 

[JEC]

For Example

Sub jec()
 If Evaluate("iserr('Welcome O19'!A1)") Then
    Sheets("Master").Copy Sheets(Sheets.Count)
    ActiveSheet.Name = "Welcome O19"
 Else
    MsgBox "name already in use"
 End If
End Sub

 

[tonywatsonhelp]

Thank you JEC,
very elegant
Thanks Tony

 

[JEC]

You’re welcome!

 

[Marc L]

Hi JEC !​

​

What could happen if the worksheet already exists and its cell A1 contains an error ?!​

​

The reason why it's better to use the worksheet function ISREF rather than ISERR …​

 

[JEC]

You are right! Thanks!?