VBA code that stops and alerts user of error

[crvazquez954]

Hi Everyone,
I'm working on a VBA code that refers to a range of cells on my "Summary" page and creates a copy of a hidden "template" tab for each name listed in this range. My question is how can I have the code stop and alert the user of a duplicate name whenever that occurs rather than cause an error and immediately jump to debug? Below is a copy of my code. Any assistance tweaking this code to prevent the error would be appreciated.

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Summary")
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Template").Visible = True
For Each c In sh2.Range("B28", sh2.Cells(Rows.Count, 2).End(xlUp))
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Next
ActiveWorkbook.Sheets("Template").Visible = False
Sheets("Summary").Select
Application.ScreenUpdating = True
End Sub

 

[crvazquez954]

I'm sure it's a simple IF statement but I'm not sure about how to write it.

 

[Danmc]

looking into it

 

[Danmc]

ok put this statement on line 2 just under your Dim Statement:

On Error GoTo ErrHandler:

put this after application.screenupdating = true statement:

Exit Sub
ErrHandler:
If Err.Number = 1004 Then MsgBox "This name already exists, please edit cell ""B28"" and provide a new name"

will that work for you?

 

[Fluff]

How about

Sub makeSheets()
   Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
   Set sh1 = Sheets("Template")
   Set sh2 = Sheets("Summary")
   Application.ScreenUpdating = False
   ActiveWorkbook.Sheets("Template").Visible = True
   For Each c In sh2.Range("B28", sh2.Cells(Rows.Count, 2).End(xlUp))
      If Evaluate("isref('" & c.Value & "'!A1)") Then
         MsgBox "Sheet " & c.Value & " already exists"
         Exit Sub
      End If
      sh1.Copy After:=Sheets(Sheets.Count)
      ActiveSheet.Name = c.Value
   Next
   ActiveWorkbook.Sheets("Template").Visible = False
   Sheets("Summary").Select
   Application.ScreenUpdating = True
End Sub

 

[crvazquez954]

You guys are the best!!!! Both Solutions worked like a charm. Thanks a bunch for your help.

 

[Fluff]

You're welcome & thanks for the feedback