Checking for existence of worksheet before creating
 

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

Checking for existence of worksheet before creating
 

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
Marc Gendron wrote:
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

Checking for existence of worksheet before creating
 

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


"Marc Gendron" wrote:


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

Checking for existence of worksheet before creating
 

I musta got lost.......

Well, I tried it this time, and it worked on the first try.

Yé !!!!

Thanks,
Marc

"Charlie" wrote:

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


"Marc Gendron" wrote:


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