How to create a sheet if it doesn't exist already?

Question

I'm using the following code to check in a workbook whether sheet1 and sheet2 exist or not. If they do not exist then they're supposed to be generated. Otherwise, nothing should happen.

My problem is the macro only works for the first iteration when neither of the worksheets exists. Once the worksheets are created I get an error. Something like "Name already exists. Choose a different one…." I don't want anything to happen if sheet1 and sheet2 already exist.

Sub Worksheet()

    Dim x As Integer, blnFound1 As Boolean, blnFound2 As Boolean
    blnFound1 = False
    blnFound2 = False

    With ThisWorkbook

        For x = 1 To .Sheets.Count

            If .Sheets(x).Name = "Sheet1" Then
                blnFound1 = True
                Exit For
            End If

            If .Sheets(x).Name = "Sheet2" Then
                blnFound2 = True
                Exit For
            End If

        Next x

        If blnFound1 = False Then
            .Sheets.Add
            With ActiveSheet
                .Name = "Sheet1"
            End With
        End If

        If blnFound2 = False Then
            .Sheets.Add
            With ActiveSheet
                .Name = "Sheet2"
            End With
        End If

    End With

End Sub

removing the `Exit For` lines might fix it. And instead of using ActiveSheet, you can use `.Sheets.Add.Name = "Sheet1"`. There is also a more efficient method to check if sheet exists https://stackoverflow.com/a/28473714/1383168 — Slai, Jun 20 '19 at 14:48
Yes, removing Exit For solved the problem. Thank you. How did you figure it out? — D3merzel, Jun 20 '19 at 14:57

Damian · Accepted Answer · 2019-06-21T07:03:34.423

4

I use a different macro on every project to handle this, so you can use it whenever you want:

Sub TestSheet(SheetName As String)

    Dim Exists As Boolean

    With ThisWorkbook
        On Error Resume Next
        Exists = (.Worksheets(SheetName).Name <> "")
        On Error GoTo 0
        If Not Exists Then
            .Sheets.Add After:=.Sheets(.Sheets.Count)
            .Sheets(.Sheets.Count).Name = SheetName
        End If
    End With

End Sub

This is how you test:

Sub Test()

    TestSheet "Sheet1"
    TestSheet "Sheet2"

End Sub

edited Jun 21 '19 at 07:03

answered Jun 20 '19 at 14:50

Damian

5,152
1
10
21

You just need to call it like this `TestSheet "YourSheetName"`. Nothing will happen if the sheet exists, the sheet will be added if not. – Damian Jun 20 '19 at 15:06
Sorry, but I still don't understand it. How can I check for example for `sheet1` **and** `sheet2` using your code? – D3merzel Jun 21 '19 at 05:34
`TestSheet "sheet1"` and `TestSheet "Sheet2"` – Damian Jun 21 '19 at 06:55
Still not clear, sorry. Can you edit it in your code, please? – D3merzel Jun 21 '19 at 07:00
1

@JohnDemian there you go. – Damian Jun 21 '19 at 07:03
Now I understand. Thank you. – D3merzel Jun 21 '19 at 07:25

Robert Todar · Answer 2 · 2019-06-21T19:44:35.243

What if you want to use the new Worksheet only if it didn't exist?

In that scenario, I would use a Try-Parse Pattern.

To do this, create a function that takes in the sheet name and a ByRef parameter that can return your newly created worksheet object.

Public Function TryCreateWorksheet(ByVal SheetName As String, Optional ByRef outWorksheet As Worksheet, Optional ByRef Source As Workbook) As Boolean

    'If workbook not passed in then set it to the activeworkbook.
    If Source Is Nothing Then
        Set Source = ActiveWorkbook
    End If

    If Not WorksheetExists(SheetName, Source) Then

        'Return true, then set outWorksheet to created worksheet and rename it.
        TryCreateWorksheet = True
        Set outWorksheet = Source.Worksheets.Add(After:=Source.Worksheets(Source.Worksheets.Count))
        outWorksheet.Name = SheetName

    End If

End Function

Here is the function for checking if the worksheet exists. It's good to be explicit in which Workbook you want to check so you don't run into any errors.

Public Function WorksheetExists(ByVal SheetName As String, ByRef Source As Workbook) As Boolean
    On Error Resume Next
    WorksheetExists = (Source.Worksheets(SheetName).Name <> "")
    On Error GoTo 0
End Function

How to use it?

If the worksheet is created then the function returns true and you can safely know you have a reference to your new worksheet.

You could use this in an if statement to see if it returns true. If so, you can now use your worksheet object. See below:

Private Sub SomeProcedure()

    Dim CreatedWs As Worksheet
    If TryCreateWorksheet("Sheet3", CreatedWs, ActiveWorkbook) = False Then
        MsgBox "Sheet already exists", vbInformation
        Exit Sub
    End If

    'Do Something with your created Ws
    Debug.Print CreatedWs.Name

End Sub

What if you'd like a unique name when the worksheet exists?

In that case, you could add a unique index to the end of the sheet names.

For example, if you have Sheet1 the next unique name would be Sheet1 (2) and so forth.

Public Function UniqueSheetName(ByVal Name As String, ByRef Source As Workbook) As String

    'Used to create a new unique name
    Dim NewName As String
    NewName = Name

    'Used to increment the name index. ie: Sheet1(1)
    Dim Index As Integer
    Index = 1

NameLoop:

    'If exists then change name to include increment (n)
    If WorksheetExists(NewName, Source) Then
        Index = Index + 1
        NewName = Name & " (" & Index & ")"
        GoTo NameLoop
    End If

    UniqueSheetName = NewName

End Function

How to create a sheet if it doesn't exist already?

2 Answers2

What if you want to use the new Worksheet only if it didn't exist?

How to use it?

What if you'd like a unique name when the worksheet exists?