Checking for existence of worksheet before creating

[Guest]

Hi everyone,

You guys have been so helpful, I can't help but ask you again to help me
with a problem I can't seem to get around.

I wanna check if a worksheet exists before creating a new worksheet. I
stole bits from other questions on this forum, and came up with :


Workbooks(tata).Activate

Set sh = Worksheets(toto)
On Error Resume Next
If sh Is Nothing Then
Worksheets.Add.Name = Worksheets(toto)
Else
sh.Activate
End If



my strings "tata" and "toto" work fine elsewhere, but I gather that I get a
"Subscript out of range" at the line : "Set sh = Worksheets(toto)"
because worksheet "toto" doesn't exist.....but that's the purpose of this
thing, to check if it exists.

Yeah, I know I should also get help to find better variable names

Thanks in advance for your help
Marc

 

[Die_Another_Day]

Move your on error resume next before the set command. Oh, and don't
forget to reset your error handling (On error goto 0)

Die_Another_Day

 

[Guest]

You didn't try the one I sent you the other day?

CreateSheet "toto"

here is the sub again:

Public Sub CreateSheet(SheetName As String)
'
' if the sheet already exists select it
'
On Error GoTo CreateNewSheet
Sheets(SheetName).Select
Exit Sub
'
' if not create it
'
CreateNewSheet:
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = SheetName
'
End Sub