Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for the existence of a worksheet | Excel Discussion (Misc queries) | |||
Checking for existence of value in another sheet | Excel Programming | |||
Checking for existence of excel | Excel Programming | |||
Checking for existence of excel | Excel Programming | |||
Checking for the Existence of a Folder | Excel Programming |