Add a new sheet to excel workbook

September 16, 2022

I am trying to add a new sheet to my workbook and I was able to find some suggestions online. I ended up using this piece of code but for some reason, it is not working. I don’t know why because to me the logic makes sense and there seems to be no problem with the syntax. I am guessing it’s something I can’t see and since I am new to VBA I could really use someone’s input.

Private Sub PopulateTaskList()

'Adds new sheet called task list
    Dim exists As Boolean
    Dim i As Integer
   
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "Task List" Then
        exists = True
        Else
            If exists = False Then
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Task List"
            End If
        End If
    Next i

End Sub

>Solution :

Use Exit Sub instead when it’s detected.

That way, if it makes it out of the loop (but not out of the sub), you know it hasn’t been created yet.

Private Sub PopulateTaskList()
    'Adds new sheet called task list
    Dim i As Integer
   
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "Task List" Then Exit Sub
    Next
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Task List"

End Sub