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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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:
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top