Test if sheet exists create if not

[sean.mcinnis]

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

 

[DoctorRaf]

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

 

[idyllicabyss]

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

 

[Argus]

Glad I could help

Sean

 

[Argus]

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

 

[Argus]

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.

 

[Argus]

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