Thread: Code Required To Avoid Error on Worksheet Insert

Worksheet Title: "Summary"
Column A, starting in row 2:
A
B
C
D

The code below effectively creates and renames four (4) worksheets "A", "B", "C" and "D". But, if I add a row with "E" and run the macro, I get an error basically saying A, B, C and D already exist so you are screwed....

I need to insert code that will allow me to create only new worksheets for the new data in column A. Thank you so much for your help.

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("Summary").Range("A2")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

Please use CODE tags when posting VBA to the board.

In answer to your question - validate existence of sheet prior to addition:

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range    
    Set MyRange = Sheets("Summary").Range("A2")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    For Each MyCell In MyRange
        If Not Evaluate("ISREF('" & MyCell.Value & "'!A1)") Then
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        End If
    Next MyCell
End Sub