Hi guys,

I like to know how to find a worksheet in a workbook in which I know
the name begins with the word "Completed". The trouble is I am not too
sure how you incorporate wildcard with the search as well, e.g. the
sheet could be called "Completed 0506"

After the search i would like to set that worksheet to a worksheet
variable as well, so i can acesss that worksheet for further data
access.

Many thanks


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

dim wks as worksheet
dim CompWks as worksheet

set CompWks = nothing
for each wks in activeworkbook.worksheets
if lcase(wks.name) like "completed *" then
set compwks = wks
exit for
end if
next wks

if compwks is nothing then
'not found
'what should happen
else
'found it!
end if

philwongnz wrote:

Hi guys,

I like to know how to find a worksheet in a workbook in which I know
the name begins with the word "Completed". The trouble is I am not too
sure how you incorporate wildcard with the search as well, e.g. the
sheet could be called "Completed 0506"

After the search i would like to set that worksheet to a worksheet
variable as well, so i can acesss that worksheet for further data
access.

Many thanks

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

Many thanks Dave,

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

Public Function SheetExists(currentWorkbook As Workbook, strSearchFo
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 Functio

--
philwongn
-----------------------------------------------------------------------
philwongnz's Profile: http://www.excelforum.com/member.php...fo&userid=2628
View this thread: http://www.excelforum.com/showthread.php?threadid=56550

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