Test for for sheets existance

F

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
 
T

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

Francis Brown said:
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) _
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top