Test for for sheets existance

[Francis Brown]

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").range
_("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

 

[Tom Ogilvy]

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").range
_("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

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").range
_("A6:A17")

For Each ADV In prange


SName = ADV.Value & "MIS"

On Error Resume Next
SheetExists = CBool(Not

Workbooks("TeamPlotter2").Worksheets(SName) _