Check if sheet exists

Question

I want to check if the sheet named "test" exists and if not, create and name that sheet to "test".

If it exists, I run a separate block of code which I haven't put up here. I have used error handling in that it ignores the error if it happens.

If Sheets("test").Name = "" Then  
'MsgBox Sheets("test").Name & "Name"  
.Worksheets.Add         After:=ThisWorkbook.Worksheets("test2")  
.ActiveSheet.Name = "test"  
End If

No matter what I do, this section of the code always runs and creates a new sheet.

The code runs properly if the sheet "test" doesn't exist already. It creates a new sheet and renames it and moves on. Obviously it doesn't rename the sheet in the other case since there's already another sheet "test" with the same name.

Duplicate of http://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists — Scott Craner, Jan 25 '16 at 15:19
Possible duplicate of [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) — puzzlepiece87, Jul 21 '17 at 21:36

score 4 · Answer 1 · answered Jan 25 '16 at 16:06

If you're not too familiar with VBA, you could use this rather than a function:

Sub checkSheet()
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "MySheet" Then
            exists = True
        End If
    Next i

    If Not exists Then
        Worksheets.Add.Name = "MySheet"
    End If
End Sub

Tragamor · Accepted Answer · 2019-12-06T13:41:58.867

Not quite sure why you're getting additional worksheets added, but I would use and external function to check whether the worksheet exists...

I would also add some error checking for "test2" so here is some code which you should be able to adapt

Sub Test()
    Dim wsName As String: wsName = "test"
    If Not WorkSheetExists(wsName) Then Worksheets.Add().Name = wsName
    If WorkSheetExists("test2") Then Worksheets(wsName).Move _
        After:=ThisWorkbook.Worksheets("test2")
End Sub

Function WorkSheetExists(ByVal strName As String) As Boolean
   On Error Resume Next
   WorkSheetExists = Not ActiveWorkbook.Worksheets(strName) Is Nothing
End Function

* EDIT *

Updated function to specify which workbook should be tested

Function WorkSheetExists(ByVal SheetName As String, Optional ByRef WorkbookToTest As Workbook) As Boolean
   On Error Resume Next
   If WorkbookToTest Is Nothing Then Set WorkbookToTest = ThisWorkbook
   WorkSheetExists = Not WorkbookToTest.Worksheets(SheetName) Is Nothing
End Function

does not work. Tried this method and also the for (loop through all worksheets), basically they do nothing. it doesn't go near the wsname sheet at all. — Prafulla Kumar Shahi, Mar 07 '16 at 21:17

score 0 · Answer 3 · answered Jan 25 '16 at 15:50

A slightly different way of achieving this would be to create a dictionary of the sheet names.

You can then use the exists function to test whether the sheet exists or not

Dim sheetNames As Object
Dim ws As Worksheet

' Create and populate dictionary
Set sheetNames = CreateObject("Scripting.Dictionary")
For Each ws In ThisWorkbook.Sheets
    sheetNames.Add ws.Name, ws.Index
Next ws

' Test if sheet exists
If Not sheetNames.Exists("test") Then
    ' If not add to workbook
    ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets("test2")).Name = "test"
    ' add sheet to dictionary
    sheetNames.Add "test", ThisWorkbook.Worksheets("test").Index
End If

score -1 · Answer 4 · answered Jan 25 '16 at 15:23

-1

Try this :

Function IsExists(name As String, Optional wb As Workbook) As Boolean
    Dim sheet As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(name)
     On Error GoTo 0
     IsExists = Not sheet Is Nothing
 End Function

answered Jan 25 '16 at 15:23

Sandesh

1,190
3
23
41

Just in case the OP has a limited knowledge of vba, how would you combine what you just posted with what the OP is trying to do with his/her code? – Scott Craner Jan 25 '16 at 15:30
I give this as a plus one - you pass the name of the sheet and workbook reference rather than look at each sheet in turn. @Scott It will return TRUE/FALSE - so `IF NOT IsExists("Sheet1") THEN` – Darren Bartrup-Cook Jan 25 '16 at 16:29
@DarrenBartrup-Cook, I know how to use it, but for a good answer it needs to show the OP how to reference the Function from the sub. – Scott Craner Jan 25 '16 at 16:30

Check if sheet exists

4 Answers4

Related