Copy sheets and remame based on range of values

[danno79]

I have the following piece of code which copies worksheets, renames them, based on values from a range and puts that value in a specified cell in the copied worksheet. This all works fine but I'd like to add fuctionality to it so that when the macro is run again it creates worksheets in the same way for "new" entries onto the list, ignoring those that already exist. (ie not returning an error by trying to create another worksheet with the same name). I'm guessing it will involve "On Error Go to" somewhre but not sure where to add this in. Any help greatly appreciated

Sub AutoAddPrograms()

    Sheets("MAIN DATA ENTRY2").Select
    Dim i As Integer
    Dim wks As Worksheet
     
    Set wks = Sheets("MAIN DATA ENTRY2")
   
    For i = Range("B40").Value To 8 Step -1 'Starts at the bottom and works up
            Sheets(5).Copy After:=Sheets(5)
            ActiveSheet.Name = wks.Cells(i, 2)
            ActiveSheet.Cells(1, 3) = wks.Cells(i, 2)
       
    Next i
     
End Sub

 

[VoG]

Perhaps

Sub AutoAddPrograms()

    Sheets("MAIN DATA ENTRY2").Select
    Dim i As Integer
    Dim wks As Worksheet
     
    Set wks = Sheets("MAIN DATA ENTRY2")
   
    For i = Range("B40").Value To 8 Step -1 'Starts at the bottom and works up
            Sheets(5).Copy After:=Sheets(5)
            On Error Resume Next
            ActiveSheet.Name = wks.Cells(i, 2)
            On Error GoTo 0
            ActiveSheet.Cells(1, 3) = wks.Cells(i, 2)
       
    Next i
     
End Sub

 

[danno79]

Thanks Peter - This prevents an error but the copy sheet5 is still created - any ideas?