Help with code that checks if a sheet exists

[wachen]

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

 

[Guest]

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

 

[Bob Flanagan]

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