VBA create worksheet if it doesn't exist

[cazdealer]

Hi,

how could I tell VBA to create a worksheet named "info" if it doesn't exist but skip that if that worksheet already exist in my workbook?

 

[Airfix9]

Sub TEST()
    
    Dim i As Integer, blnFound As Boolean
    blnFound = False
    
    With ThisWorkbook
        For i = 1 To .Sheets.Count
            If .Sheets(i).Name = "Info" Then
                blnFound = True
                Exit For
            End If
        Next i
        
        If blnFound = False Then
            .Sheets.Add
            With ActiveSheet
                .Name = "Info"
            End With
        End If
    End With
    

End Sub

 

[Legacy 264765]

I know this i quite old - but can someone please explain how with and this workbook works ?

 

[dmt32]

Hi,

how could I tell VBA to create a worksheet named "info" if it doesn't exist but skip that if that worksheet already exist in my workbook?

another way:

Sub CheckSheet()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets("Info")
    If Err.Number = 9 Then
        Set ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
        ws.Name = "Info"
    End If
    With ws
        'do stuff
    End With
End Sub

Dave

 

[mikerickson]

The With ThisWorkbook like is matched with an End With line. Everything inbetween those lines is qualified to the ThisWorkbook object.

With ThisWorkbook
    For i = 1 To .Sheets.Count
        If .Sheets(i).Name = "Info" Then
            blnFound = True
            Exit For
        End If
    Next i
End With

is the same as

For i = 1 To ThisWorkbook.Sheets.Count
    If ThisWorkbook.Sheets(i).Name = "Info" Then
        blnFound = True
        Exit For
    End If
Next i

 

[MARK858]

duplicate post

 

[MARK858]

LAST EDIT: Mike was faster