problem finding named sheet

P

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
 
J

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
 

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