Excel – VBA create new Worksheet if not exists

excelvba

I am currently working on a VBA macro, that takes data from a Worksheet and copies it to another.
If the destination Worksheet does not exist it should create it and than write the data from my array.

Problem:
I have a function to test if the worksheet already exists.
If it is the case my macro will successfully write the data i want. But if the worksheet doesnt exist VBA is displaying the error you can see below.
In the list Workbook.Worksheets is no Sheet named like this but I get that error anyway.

Here is my relevant code:
(If something is missing for understanding the problem I can fill in the missing part in too)

Function sheetExists(sheetToFind As String) As Boolean
    Dim Sheet As Worksheet
    For Each Sheet In Worksheets
        If sheetToFind = Sheet.Name Then
            sheetExists = True
            Exit Function
        End If
    Next Sheet
    sheetExists = False
End Function

In my main Sub I used this code:

If sheetExists("SheetName") = False Then
    Dim newSheet As Worksheet
    With ThisWorkbook
        .Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "SheetName"
    End With
End If

The exact error:

1004 Cannot rename a sheet to the same name as another sheet, a reference object library, or a workbook referenced by Visual Basic

First it was executing successfully but after I deleted the sheet manually the error occurred.

Thanks for any help 🙂

Best Answer

Specify in which workbook to look at:

For Each Sheet In ThisWorkbook.Sheets

also not that it has to be Sheets and not Worksheets, because Worksheets only contains worksheets but Sheets also contains charts, etc. So we have to check these names too!
(Sheet then has to be Dim Sheet As Object)

You can make your function more flexible:

Function sheetExists(sheetToFind As String, Optional InWorkbook As Workbook) As Boolean
    If InWorkbook Is Nothing Then Set InWorkbook = ThisWorkbook

    Dim Sheet As Object
    For Each Sheet In InWorkbook.Sheets
        If sheetToFind = Sheet.Name Then
            sheetExists = True
            Exit Function
        End If
    Next Sheet
    sheetExists = False
End Function

so you can call it:

  • sheetExists("SheetName") to use ThisWorkbook by default, or
  • sheetExists("SheetName", Workbooks("MyWorkbook")) to specify a specific workbook.

Alternatively you can use 

Function sheetExists(sheetToFind As String, Optional InWorkbook As Workbook) As Boolean
    If InWorkbook Is Nothing Then Set InWorkbook = ThisWorkbook

    On Error Resume Next  
    sheetExists = Not InWorkbook.Sheets(sheetToFind) Is Nothing
End Function

which can be a bit faster if there are many sheets in a workbook.

Related Topic