Worksheets Exists Code...
 

Hi,
I'm using the code below to find out if a worksheet named "Memo" exists in
the active workbook.
If the code find a worksheet named "Memo"... it returns True.
It's working great..but now I need to change the code to find out if there
are any worksheets in the active workbook that start with the name "Memo"
There can be many worksheets with the name "Memo" added to this file....and
appear as... Memo, Memo(2), Memo (3) and so on...
I'm not sure what needs to be changed in this code to do this..

Public Function WSExist(Memo As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Object
On Error Resume Next
WSExist = False
Set objWorksheet = ActiveWorkbook.Sheets("Memo")
If Err = 0 Then
WSExist = True
End If
End Function

Any help is greatly apprecaited..

Thanks in advance!
Kimberly :)

Worksheets Exists Code...
 

Hi Kimberly,

Try this:

Public Function WSExist(MyStr As String) As Boolean
'returns true if worksheet exists in the active workbook
For Each ws In ActiveWorkbook.Worksheets
If UCase(ws.Name) Like UCase(MyStr) & "*" Then
WSExist = True
Exit For
End If
Next ws
End Function


Regards,
KL



"KimberlyC" wrote in message
...
Hi,
I'm using the code below to find out if a worksheet named "Memo" exists in
the active workbook.
If the code find a worksheet named "Memo"... it returns True.
It's working great..but now I need to change the code to find out if there
are any worksheets in the active workbook that start with the name "Memo"
There can be many worksheets with the name "Memo" added to this
file....and
appear as... Memo, Memo(2), Memo (3) and so on...
I'm not sure what needs to be changed in this code to do this..

Public Function WSExist(Memo As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Object
On Error Resume Next
WSExist = False
Set objWorksheet = ActiveWorkbook.Sheets("Memo")
If Err = 0 Then
WSExist = True
End If
End Function

Any help is greatly apprecaited..

Thanks in advance!
Kimberly :)

Worksheets Exists Code...
 

One way:

Option Explicit

Public Function WSExist2(Memo As String, _
Optional wkbk As Workbook = Nothing) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Worksheet

If wkbk Is Nothing Then
Set wkbk = ActiveWorkbook
End If

WSExist2 = False
For Each objWorksheet In wkbk.Worksheets
If LCase(Left(objWorksheet.Name, Len(Memo))) = LCase(Memo) Then
WSExist2 = True
Exit For
End If
Next objWorksheet

End Function


Sub testme()
Dim wkbk As Workbook
Set wkbk = Workbooks("book2.xls")
MsgBox WSExist2("Memo", wkbk)
End Sub





KimberlyC wrote:

Hi,
I'm using the code below to find out if a worksheet named "Memo" exists in
the active workbook.
If the code find a worksheet named "Memo"... it returns True.
It's working great..but now I need to change the code to find out if there
are any worksheets in the active workbook that start with the name "Memo"
There can be many worksheets with the name "Memo" added to this file....and
appear as... Memo, Memo(2), Memo (3) and so on...
I'm not sure what needs to be changed in this code to do this..

Public Function WSExist(Memo As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Object
On Error Resume Next
WSExist = False
Set objWorksheet = ActiveWorkbook.Sheets("Memo")
If Err = 0 Then
WSExist = True
End If
End Function

Any help is greatly apprecaited..

Thanks in advance!
Kimberly :)

--

Dave Peterson

Worksheets Exists Code...
 

Thanks to you both!!!


"KimberlyC" wrote in message
...
Hi,
I'm using the code below to find out if a worksheet named "Memo" exists in
the active workbook.
If the code find a worksheet named "Memo"... it returns True.
It's working great..but now I need to change the code to find out if there
are any worksheets in the active workbook that start with the name "Memo"
There can be many worksheets with the name "Memo" added to this
file....and
appear as... Memo, Memo(2), Memo (3) and so on...
I'm not sure what needs to be changed in this code to do this..

Public Function WSExist(Memo As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Object
On Error Resume Next
WSExist = False
Set objWorksheet = ActiveWorkbook.Sheets("Memo")
If Err = 0 Then
WSExist = True
End If
End Function

Any help is greatly apprecaited..

Thanks in advance!
Kimberly :)