You already have a solution. However, FWIW, below is a more general
purpose one:

Option Explicit

Function SheetExists(SheetName As String, _
Optional WBName As String) As Boolean
On Error Resume Next
If WBName = "" Then _
SheetExists = _
Not ActiveWorkbook.Sheets(SheetName) Is Nothing _
Else _
SheetExists = _
Not Workbooks(WBName).Sheets(SheetName) Is Nothing
End Function

Sub testIt()
MsgBox SheetExists("sheet1") & "," & SheetExists("sheet 1")
MsgBox SheetExists("sheet1", "findall.xls") & "," _
& SheetExists("sheet 1")
MsgBox SheetExists("sheet1", "findall.xls") & "," _
& SheetExists("sheet 1", "findall.xls")
End Sub



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "PC" <paulm dot c @
iol dot ie says...
Hi,

I'm using a udf to test for the existance of a worksheet and to perform an
action if the sheet is found. The code for the function is as follows:


Function WksExists(ByVal wksName As String) As Boolean
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function


In my procedure I call the function using:


If WksExists("1 (2)") = True Then ' Test for existance of sheet
Sheets("1 (2)").Select ' If true then select sheet
Sheets("1 (2)").Name = i + 1 ' Rename sheet with the value of i
+1
End If


This works fine if the sheet "1 (2)" actually does exist. The probelm is if
the sheet "1 (2)" doesn't exist I get a "Run-Time Error 9" - "Subscript out
of Range".

Any idea why this is happening and how I would over come it.

Thanks in advance

..pc