Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code that checks if a sheet exists
I use the following code to check if a worksheet exists. It works OK if the
worksheet does exist, However, if the worksheet does not exist, then the "Set" statement gives a "Subscript out of range" error. Can someone please help? Thanks. Sub checkit () Dim NewTabName As String, WkSht As Worksheet NewTabName = "1-23-04" Set WkSht = Workbooks(CurBookName).Worksheets(NewTabName) If Not WkSht Is Nothing Then MsgBox "The worksheet exists" Else Workbooks(CurBookName).Activate Workbooks(CurBookName).Sheets.Add.Name = NewTabName End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code that checks if a sheet exists
Wache
One way is to add the lin On Error Resume Nex before the Set command Also, you should initialise the CurBookName variable Ton ----- wachen wrote: ---- I use the following code to check if a worksheet exists. It works OK if th worksheet does exist, However, if the worksheet does not exist, the the "Set" statement gives a "Subscript out of range" error Can someone please help? Thanks Sub checkit ( Dim NewTabName As String, WkSht As Workshee NewTabName = "1-23-04 Set WkSht = Workbooks(CurBookName).Worksheets(NewTabName If Not WkSht Is Nothing The MsgBox "The worksheet exists Els Workbooks(CurBookName).Activat Workbooks(CurBookName).Sheets.Add.Name = NewTabNam End I End Su |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code that checks if a sheet exists
Wachen, make the following changes:
On error resume next Set WkSht = Workbooks(CurBookName).Worksheets(NewTabName) On error goto 0 The first on error statement allows the following line to run with an error. The second turns off this feature. Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "wachen" wrote in message news:L0XVb.13830$QA2.24788@attbi_s52... I use the following code to check if a worksheet exists. It works OK if the worksheet does exist, However, if the worksheet does not exist, then the "Set" statement gives a "Subscript out of range" error. Can someone please help? Thanks. Sub checkit () Dim NewTabName As String, WkSht As Worksheet NewTabName = "1-23-04" Set WkSht = Workbooks(CurBookName).Worksheets(NewTabName) If Not WkSht Is Nothing Then MsgBox "The worksheet exists" Else Workbooks(CurBookName).Activate Workbooks(CurBookName).Sheets.Add.Name = NewTabName End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check if sheet exists | Excel Discussion (Misc queries) | |||
Micro that checks if file exists | Excel Worksheet Functions | |||
Sheet name already exists | Excel Discussion (Misc queries) | |||
How can I know if a sheet exists ? | Excel Programming | |||
check if sheet exists | Excel Programming |