VBA to add sheet; with name; after checking first

[Guest]

XL 2003 & 97

Almost have it. (I think)

My "If Not" syntax below is incorrect.
The middle line works fine.

If Not Sheets.Name("New") Then
Sheets.Add.Name = "New"
End If

The code is to
1) check the workbook for a sheet name first
2) If the sheet does not exist then add a sheet
3) Name the sheet "New"

Is there a better way?

TIA Dennis

 

[Dave Peterson]

Chip Pearson posted a nice function that you can use (especially nice if you
have to this lots of places in your code):

Option Explicit
Sub testme()
If WorksheetExists("New", ActiveWorkbook) Then
'do nothing
Else
ActiveWorkbook.Worksheets.Add.Name = "New"
End If
End Sub

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

 

[Guest]

Perfect!

Thanks Dave and also to all of those who help the rest of us learn more.