Test for for sheets existance
 

Hi

I'm trying to use the following code to loop through the defined range. Take
the cell value and concatenate with the term MIS.

Test if a sheet exists named the same as the concatenated term.

Then use message box to display the sheet name and then True or False.

The problem I am having with my code is that in tests it works fine upto it
finds a sheet that is True. Then even if no further sheets exist the
SheetExists variable remains True.

The Message box code will eventually be replaced by code to e-mail the sheet
if it does exist.

Can some one pont out where i went wrong and explain how to correct.

Thanks in advance.

Francis.

Public Sub testexist()
Dim SheetExists As Boolean
Dim prange As range
Dim SName As String
Set prange = Workbooks("TeamPlotter2").Worksheets("MainPage").r ange
_("A6:A17")

For Each ADV In prange


SName = ADV.Value & "MIS"

On Error Resume Next
SheetExists = CBool(Not Workbooks("TeamPlotter2").Worksheets(SName) _
Is Nothing)
On Error GoTo 0
MsgBox SName & " " & SheetExists
Next ADV
End Sub

Test for for sheets existance
 

This works for me.

Public Sub testexist()
Dim SheetExists As Boolean
Dim prange As range
Dim SName As String
Set prange = Workbooks("TeamPlotter2").Worksheets("MainPage").r ange
_("A6:A17")

For Each ADV In prange


SName = ADV.Value & "MIS"
SheetExists = False
On Error Resume Next
SheetExists = CBool(Not Workbooks("TeamPlotter2").Worksheets(SName)
_
Is Nothing)
On Error GoTo 0
MsgBox SName & " " & SheetExists
Next ADV
End Sub


--
Regards,
Tom Ogilvy

"Francis Brown" wrote in message
...
Hi

I'm trying to use the following code to loop through the defined range.
Take
the cell value and concatenate with the term MIS.

Test if a sheet exists named the same as the concatenated term.

Then use message box to display the sheet name and then True or False.

The problem I am having with my code is that in tests it works fine upto
it
finds a sheet that is True. Then even if no further sheets exist the
SheetExists variable remains True.

The Message box code will eventually be replaced by code to e-mail the
sheet
if it does exist.

Can some one pont out where i went wrong and explain how to correct.

Thanks in advance.

Francis.

Public Sub testexist()
Dim SheetExists As Boolean
Dim prange As range
Dim SName As String
Set prange = Workbooks("TeamPlotter2").Worksheets("MainPage").r ange
_("A6:A17")

For Each ADV In prange


SName = ADV.Value & "MIS"

On Error Resume Next
SheetExists = CBool(Not
Workbooks("TeamPlotter2").Worksheets(SName) _
Is Nothing)
On Error GoTo 0
MsgBox SName & " " & SheetExists
Next ADV
End Sub