If Then Else

[Jabberwokki]

Hi there and apologies cos i'm sure this has been answered many times before!

I have some code that takes a name from a list drop down cell and creates a sheet with that name. The code then proceeds to copy various info onto that new sheet. However, if the sheet name already exists excel throws its normal error message (obviously). I'm looking to add some code to the following to do a pre check for the name, return a message box with "the name already exists, please select a different one" and exit OR continue if the name doesn't exist.

Any assistance greatly appreciated.

D

Sub CreateSheet()


    Worksheets("Template").Visible = True
    Worksheets("Front").Activate
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Range("N2").Value
   
    Sheets("Template").Select
    Cells.Select
    Selection.Copy
   
    Sheets("Front").Select
    Sheets(Range("N2").Value).Activate
   
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Sheets("Template").Select
    Cells.Select
    Selection.Copy
   
    Sheets("Front").Select
    Sheets(Range("N2").Value).Activate
   
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
       
    Range("F1:H1,V1").Select
    Range("V1").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
   
    Sheets("Template").Select
    ActiveSheet.Shapes.Range(Array("Group 45")).Select
    Selection.Copy
    Sheets("Front").Select
    Sheets(Range("N2").Value).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 12
    Selection.ShapeRange.IncrementTop 16.5
    Range("A5").Select
    ActiveWindow.FreezePanes = True
    Range("M6").Select
    ActiveSheet.Protect
   
    Worksheets("Template").Visible = False
    Worksheets("Front").Activate
   
End Sub

 

[cogswel__cogs]

You could probably use
on error to ask for a suitable name.

This might work better as it will default if the tab already exists but will also default if the string contains something unusable for the file name as well.

 

[Jabberwokki]

You could probably use
on error to ask for a suitable name.

This might work better as it will default if the tab already exists but will also default if the string contains something unusable for the file name as well.

Thanks for the advice cogs. Because the name is selected from a drop down box, i need the message box to let a user know they need to select a different name....

 

[Fluff]

How about

    Dim ShtName As String

    Worksheets("Template").Visible = True
    Worksheets("Front").Activate
    ShtName = Range("N2").Value
    If ShtName = "" Then Exit Sub
    If Evaluate("isref('" & ShtName & "'!a1)") Then
       MsgBox "Sheet " & ShtName & " already exists"
       Exit Sub
    End If
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ShtName

 

[Jabberwokki]

How about

    Dim ShtName As String

    Worksheets("Template").Visible = True
    Worksheets("Front").Activate
    ShtName = Range("N2").Value
    If ShtName = "" Then Exit Sub
    If Evaluate("isref('" & ShtName & "'!a1)") Then
       MsgBox "Sheet " & ShtName & " already exists"
       Exit Sub
    End If
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ShtName

Hi Fluff...That's perfect and works a treat. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.