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
|