problem finding named sheet

[Patricia D]

I have created a sheet using VBA. When I try to select the sheet, it doesn't
work, but if I try to name anew sheet with same name, an error occurs.

This code was originally used to create sheet (Dept is a string)
'create new dept sheet
Set NewSheet = Worksheets.Add
NewSheet.Name = Dept

This code is trying to test for presence of sheet, but testpagevalue does
not pick up value in the existing sheet, so gives error?

Private Function SheetExists(sname) As Boolean
' True if sheet exists in the active workbook and create if not present
Dim testPageValue As Variant 'use to test for page presence

'test if destination sheet exists
On Error Resume Next
Err.Clear
' any cell will do
testPageValue = Worksheets(sname).Range("A1").Value
If Err.Number <> 0 Then
'page does not exist, create it
SheetExists = False
Err.Clear
On Error GoTo 0
Worksheets.Add ' add sheet, it gets selected
'can fail if destSheet is not a valid sheet name!
ActiveSheet.Name = sname ' name it
Else
SheetExists = True
End If
End Function

 

[Jim Thomlinson]

Here is a sheet exists function that you can use...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

Note that it does not create the sheet if one does not exist. That should be
done outside of the function within a sub procedure. Generally speaking (IMO)
in VBA functions should only return values. They should not create sheets or
other such side effects.

If sheetsexists("dept") = false then
'create your sheet
end if