determine if a worksheet exists

[bobaol]

Hello,

I want to find a way to determine if the worksheet "closed" currently exist in my workbook. If the worksheet does exist, then do nothing. If the worksheet does not exist, then insert a new worksheet and name it "closed"

I can insert now, but i don't know how to find if it currently exist.

Sub Macro1()
Sheets.Add
ActiveSheet.Select
ActiveSheet.Name = "closed"
End Sub

thanks in advance.

- bobaol

 

[Jim North]

if the variable strWkshtName contains the name of the worksheet you are looking for:

<pre>
WSExist= False
Set objWorksheet = ActiveWorkbook.Sheets(strWkshtName)

If Err = 0 Then
WSExist= True
End If

</pre>

 

[Andrew Poulsom]

Like this:

Dim WS As Worksheet
On Error Resume Next
Set WS = Worksheets("Closed")
If Err <> 0 Then
   Worksheets.Add.Name = "Closed"
End If

 

[Legacy 98055]

If you don't mind a little bit of extra effort, create a reusable function which you can use in other projects. This function will determine if a certain worksheet exists in any open workbook. I edited your macro1 procedure to call the function properly.

Tom
<pre>

Sub Macro1()
Select Case shExists(ActiveWorkbook.Name, "closed")
Case 1: MsgBox "The workbook name given is invalid or has not yet been saved."
Case 2: Worksheets.Add.Name = "closed"
End Select
End Sub


Public Function shExists(wbName As String, shName As String) As Integer
'returns 0 if the sheet exists, 1 if the workbook is invalid,
'2 if the worksheet is invalid
Dim Test_Exist As Integer

On Error GoTo NoBook
Test_Exist = Workbooks(wbName).Windows.Count
On Error GoTo 0

On Error GoTo NoSheet
Test_Exist = Workbooks(wbName).Sheets(shName).Index

Exit Function
NoBook:
Err.Clear
shExists = 1
Exit Function

NoSheet:
Err.Clear
shExists = 2

End Function

</pre>