Checking for existence of worksheet before creating

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top