VBA code that stops and alerts user of error

crvazquez954

New Member
Joined
Jul 9, 2019
Messages
23
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Danmc

New Member
Joined
Nov 19, 2015
Messages
33
ok put this statement on line 2 just under your Dim Statement:

Code:
On Error GoTo ErrHandler:
put this after application.screenupdating = true statement:

Code:
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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,762
Office Version
365
Platform
Windows
How about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,762
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,510
Messages
5,487,310
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top