My suggestion actually used a worksheet variable -- compwks.

You may want to make sure you have:
option compare text
at the top of the module

Or make sure you pass the correct case (or use ucase/lcase) in your function.

philwongnz wrote:

Many thanks Dave,

I was lucky enough to came up with a solution just before i recheck
anyone has replied, so I can post the solution to my question. Here's
what I've done, which is almost the same as your solution, except the
helper methods returns a worksheet object rather than a boolean. As I
need to do several searches on different workbooks I have used a
workbook as a criteria for this function, rather than activiting the
workbook prior to my search.

Public Function SheetExists(currentWorkbook As Workbook, strSearchFor
As String) As Worksheet
Dim tempWks As Worksheet

For Each tempWks In currentWorkbook.Worksheets
If tempWks.Name Like strSearchFor Then
Set SheetExists = tempWks
Exit Function
Else
Set SheetExists = Nothing
End If
Next tempWks
End Function

--
philwongnz
------------------------------------------------------------------------
philwongnz's Profile: http://www.excelforum.com/member.php...o&userid=26283
View this thread: http://www.excelforum.com/showthread...hreadid=565508

--

Dave Peterson