Test to see if a worksheet exists

[CT]

Sometimes when I try to add a new sheet ...
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Input"
I fail because there is already a worksheet named "Input".

So, before adding the new sheet, I do ...
For Each MyWorksheet In Worksheets
If MyWorksheet.Name = "Input" Then
Application.DisplayAlerts = False
Worksheets("Input").Delete
Application.DisplayAlerts = True
End If
Next MyWorksheet

Is there a more simple way to check to see if the "Input" sheet exists?

 

[CT]

Thanks, Chip

Try something like the following;

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

You can then call this function in code as follows:

If WorksheetExists("Sheet123") = True Then
' sheet exists
Else
' sheet does not exist
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)