delete sheet if it exist

[Art Parra]

how do I find out if a sheet exist to delete it? I've tried:

if not isnull(sheets("myname")) then
sheets("myname").delete
end if

thanks,

 

[Ron de Bruin]

Hi Art

You can test it like this with a function

Sub Sheet_Test()
Dim SName As String
If SheetExists("test") = True Then
'code to delete the sheet
Else
'do nothing
End If
End Sub

Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

 

[Dave Peterson]

application.displayalerts = false
on error resume next
worksheets("Myname").delete
on error goto 0
application.displayalerts = true

If the sheet doesn't exist, then the error is ignored.

(The .displayalerts stuff stops the confirmation dialog from showing up.)

=====
Chip Pearson posted this function:


Function WorksheetExists(SheetName As Variant, _
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

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then
application.displayalerts = false
worksheets("Myname").delete
application.displayalerts = true
end if