Error Worksheet Exists - need code to continue to next name if it does

Hi,

I have the following code that is used to generate worksheets automatically from a listing of names on the "Master Provider List". It works perfectly, but I have found that sometimes I need to add more names to the list. I need the code to look at the list and if the worksheet related to the name already exists, continue on to the next name and create the worksheet. I have never trapped an error before, so not sure how to add that to my code.

Private Sub Create_PList_Click()
'creates the provider's sheets, names the sheet, and enters sheet name in c4 of the new sheet
Dim i As Integer
    Dim ws As Worksheet
    Dim Sh As Worksheet
    Set ws = Sheets("Audit Temp")
    Set Sh = Sheets("Master Provider List")
    
    Application.ScreenUpdating = 0
    Sheets("Audit Temp").Visible = True
    For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
        Sheets("Audit Temp").Copy After:=ws
        ActiveSheet.Name = Sh.Range("A" & i).Value
        ActiveSheet.Cells(4, 3) = Sh.Cells(i, 1)
    Next i
    Sheets("Audit Temp").Visible = False
    Sheets("Master Provider List").Activate

End Sub

Thanks so much in advance

C

Using On Error Resume Next would ignore worksheet naming errors, but it would also leave you with un-named copies of your template worksheet.

Try this...

Private Sub Create_PList_Click()
'creates the provider's sheets, names the sheet, and enters sheet name in c4 of the new sheet
    Dim cell As Range
    Dim wsAT As Worksheet
    Dim wsMPL As Worksheet
    
    Set wsAT = Sheets("Audit Temp")
    Set wsMPL = Sheets("Master Provider List")
    
    Application.ScreenUpdating = False
    
    For Each cell In wsMPL.Range("A3", wsMPL.Range("A" & Rows.Count).End(xlUp))
        If Not Evaluate("ISREF('" & cell.Value & "'!A1)") Then  'Test if worksheet name exists
            wsAT.Copy After:=wsAT
            ActiveSheet.Visible = True
            ActiveSheet.Name = cell.Value
            ActiveSheet.Cells(4, 3) = cell.Value
        End If
    Next cell
    
    Sheets("Master Provider List").Activate
    Application.ScreenUpdating = True
    
End Sub

It worlked for me.

Try this...

Private Sub Create_PList_Click()
'creates the provider's sheets, names the sheet, and enters sheet name in c4 of the new sheet
    Dim cell As Range
    Dim wsAT As Worksheet
    Dim wsMPL As Worksheet
    
    Set wsAT = Sheets("Audit Temp")
    Set wsMPL = Sheets("Master Provider List")
    
    Application.ScreenUpdating = False
    
    wsAT.Visible = xlSheetVisible
    For Each cell In wsMPL.Range("A3", wsMPL.Range("A" & Rows.Count).End(xlUp))
        If Not Evaluate("ISREF('" & cell.Value & "'!A1)") Then  'Test if worksheet name exists
            wsAT.Copy After:=wsAT
            ActiveSheet.Visible = True
            ActiveSheet.Name = cell.Value
            ActiveSheet.Cells(4, 3) = cell.Value
        End If
    Next cell
    wsAT.Visible = xlSheetHidden
    Sheets("Master Provider List").Activate
    Application.ScreenUpdating = True
    
End Sub