Test if sheet exists create if not
 

I am using Excel 2003 I am trying to test is a sheet exist and if not
create the sheet.

I have search the and have found and edited the following in a moduel.
I have been testing this but it is always returning False. Unless
filter_by = "Sheet1"

Function SheetExists(SheetName As String) As Boolean
On Error Resume Next
SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name) )
End Function

Sub test()
Dim filter_by As String
filter_by = "test"

If SheetExists(filter_by) = False Then
Worksheets.Add.Name = filter_by
Else
' do something else
End If

End Sub

--------------------------------------------------------
Thanks in advance

Sean

Test if sheet exists create if not
 

It works just fine on Excel 2002 - in fact, I think I'll borrow your
bit of code and use it myself!

Test if sheet exists create if not
 

Try replacing
SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name) )

with
dim SheetExists as boolean
If Len(ThisWorkbook.Worksheets(SheetName).Name) 0 then
SheetExists = true
else
SheetExists = false
end if

Test if sheet exists create if not
 

Glad I could help :)

Sean

Test if sheet exists create if not
 

Again if I use

dim SheetExists as boolean
If Len(ThisWorkbook.Worksheets("Shhet1").Name) 0 then
SheetExists = true
else
SheetExists = false
end if

it works.

But if I use

dim SheetExists as boolean
If Len(ThisWorkbook.Worksheets("test").Name) 0 then
SheetExists = true
else
SheetExists = false
end if

I am get a runtime error 9 subscript out of range.


Sean

Test if sheet exists create if not
 

To added some more detail.

I have a workbook where each tab is named with the customer name. I get
a list of customer names from a DB query and use:

Sheets("Customer_Array").Select
Cells(namerow, namecol).Select
filter_by = ActiveCell


what I need it the macro to add a new sheet with the customer name
atained from the filter_by variable it it does not already exist.

Test if sheet exists create if not
 

This change made the function worked,

Function SheetExists(SheetName As String) As Boolean
On Error Resume Next
SheetExists = Len(Sheets(SheetName).Name)
End Function

Sean