How to use error handling in vba to skip duplicate sheet names?

[SirSocks]

The code below copy's the "Template" and renames based on a list. It works perfectly fine until there is a duplicate name.
How can a message box be used to indicate that an error occurred, and skip the sheet that contained the duplicate name?

I'm fairly new to VBA, an explanation on how to resolve this error would be very helpful.

Here's the code I'm using....

Option Explicit
Sub NewSheets()
Dim xRg As Excel.Range
Dim i As Integer
Dim ws As Worksheet
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim response As String
Set ws = Sheets("Template")
Set sh1 = Sheets("Sheet_Names")
Set sh2 = Sheets("Data_Sheet")
Application.ScreenUpdating = 0
 For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
 Sheets("Template").Copy before:=sh2
    ActiveSheet.Name = sh1.Range("A" & i).Value
        If Err.Number = 1004 Then 'name already used
        response = MsgBox("This sheet will be skipped as the name arelady exists", vbYesNo)
        If response = vbNo Then GoTo NextIteration
        End If

NextIteration:
 Next i
End Sub

 

[dmt32]

Hi
welcome to forum
Check if the sheet name exists before the copy procedure

Try this update to your code & see if does what you want

Option Explicit
Sub NewSheets()
   
    Dim i           As Long
    Dim SheetName   As String
    Dim wsTemplate  As Worksheet
    Dim sh1         As Worksheet, sh2 As Worksheet
   
    With ThisWorkbook
        Set wsTemplate = .Worksheets("Template")
        Set sh1 = .Worksheets("Sheet_Names")
        Set sh2 = .workheets("Data_Sheet")
    End With
   
    Application.ScreenUpdating = False
    For i = 1 To sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row
        SheetName = sh1.Range("A" & i).Value
        If Not Evaluate("ISREF('" & SheetName & "'!A1)") Then
            wsTemplate.Copy before:=sh2
            ActiveSheet.Name = SheetName
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Dave

 

[JEC]

Sub jec()
 Dim it
 Application.ScreenUpdating = False
 For Each it In Sheets("Sheet_Names").Cells(1, 1).CurrentRegion
    If Not Evaluate("isref('" & it & "'!A1)") Then
        Sheets("Template").Copy Sheets("Data_Sheet")
        ActiveSheet.Name = it
    End If
 Next
End Sub

 

[BSALV]

suppoe you have a new sheet, called "NewSheet" and you want to rename it to "MySheet" if that one doesn't exist or "MySheet(xx)" where xx as an incremental index number

Sub NewSheets()
     Dim sh As Worksheet, i As Integer

     Do
          Set sh = Nothing
          On Error Resume Next
          Set sh = Sheets("MySheet" & IIf(i = 0, "", "(" & Format(i, "00\)")))     'try to set sh as the worksheet "MySheet", or "MySheet(01)", "MySheet(02)", ....
          On Error GoTo 0
          If Not sh Is Nothing Then i = i + 1                   'if succesfull, meaning that sheet exists, add 1 to the index
     Loop While Not sh Is Nothing And i <= 99                   'loop *** long as unsuccesfull or index>99

     If i < 99 Then Sheets("NewSheet").Name = "MySheet" & IIf(i = 0, "", "(" & Format(i, "00\)"))     'rename your sheet

End Sub

 

[SirSocks]

@dmt32 Thats perfect! Thank you very much.

 

[SirSocks]

@JEC That is also very helpful. Thank you.

 

[dmt32]

@dmt32 Thats perfect! Thank you very much.

most welcome glad we were able to help - appreciate the feedback

Dave