determine if a worksheet exists

bobaol

Board Regular
Joined
Jun 3, 2002
Messages
117
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this:

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

Legacy 98055

Guest
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>
 

Forum statistics

Threads
1,144,220
Messages
5,723,085
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top