CODEWITHSUNDEEP
excelvba
Prafulla Kumar Shahi
Prafulla Kumar Shahi

Reputation: 95

Check if sheet exists

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.

Upvotes: 0

Views: 16427

Answers (4)

Tragamor
Tragamor

Reputation: 3634

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

Upvotes: 1

Josh Whitfield
Josh Whitfield

Reputation: 321

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

Upvotes: 4

Tom
Tom

Reputation: 9878

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

Upvotes: 0

Sandesh
Sandesh

Reputation: 1222

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

Upvotes: -1

Related Questions