Delete Sheets if exists and create new sheet

[melewie]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
First off I know there has been a quite a few post about this but I am really struggling with it (not sure if its because its Monday or I have the coding skills of a drunken chimp)<o></o>
<o></o>
I am trying to create a new sheet if the sheet already exists then delete it and create a new sheet of the same name.<o></o>
<o></o>
Worksheets.Add.Name = "Missing Data"<o></o>
<o></o>
If error delete the bloody thing and create a new one!!<o></o>
<o></o>
Trying to make this generic so it could used 26 times (with different sheet names every time) so I am trying to create a sub I can call to do this.<o></o>
<o></o>
Worksheets.Add.Name = "Missing Data"<o></o>
<o></o>
If error call answer<o></o>
<o></o>
Any help would be greatly appreciated

 

[VoG]

Try this function

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

then in your code

If WorksheetExists("Missing Data") Then
    Application.DisplayAlerts = False
    Worksheets("Missing Data").Delete
    Application.DisplayAlerts = True
End If
Worksheets.Add.Name = "Missing Data"

 

[xld]

Sub Test3()
    CreateNewSheet "Sheet3"
End Sub

Function CreateNewSheet(SheetName As String)

    On Error Resume Next
    Application.DisplayAlerts = False
    
    Worksheets(SheetName).Delete
    Worksheets.Add.Name = SheetName
    
    Application.DisplayAlerts = True
    
End Function

 

[ScottR]

Its bad strategy to try and force an error here when you don't need to. There are other ways to check if a sheet exists.

Sub AddSheet(sName As String)

Dim WS As Worksheet, RS As New Worksheet

For Each WS In ThisWorkbook.Worksheets
    If WS.Name = sName Then 'Check if it Exists and delete if it does
        Application.DisplayAlerts = False 'Stops excel asking you for manual confirmation
        WS.Delete
        Application.DisplayAlerts = True
    End If
Next

Set RS = ThisWorkbook.Worksheets.Add
RS.Name = sName 'New worksheet with the specified name
'Do more stuff below this line

End Sub

Sub CallIt()

Call AddSheet("Missing Data")

End Sub

 

[xld]

Its bad strategy to try and force an error here when you don't need to. There are other ways to check if a sheet exists.

Sub AddSheet(sName As String)

Dim WS As Worksheet, RS As New Worksheet

For Each WS In ThisWorkbook.Worksheets
    If WS.Name = sName Then 'Check if it Exists and delete if it does
        Application.DisplayAlerts = False 'Stops excel asking you for manual confirmation
        WS.Delete
        Application.DisplayAlerts = True
    End If
Next

Set RS = ThisWorkbook.Worksheets.Add
RS.Name = sName 'New worksheet with the specified name
'Do more stuff below this line

End Sub

Sub CallIt()

Call AddSheet("Missing Data")

End Sub

This is a joke, no?

 

[ScottR]

I admit the first response, wrapping the check in a separate function, is a more optimal method but why would it be a joke? It works.

 

[xld]

I admit the first response, wrapping the check in a separate function, is a more optimal method but why would it be a joke? It works.

Yes it works, but the joke, to me view, is suggesting that it is ... bad strategy to try and force an error here when you don't need to .... Controlled error handling, such as in the solutions that Vog and I offered, is a far more optimal solution than looping.

 

[ScottR]

Yes it works, but the joke, to me view, is suggesting that it is ... bad strategy to try and force an error here when you don't need to .... Controlled error handling, such as in the solutions that Vog and I offered, is a far more optimal solution than looping.

I wrote that before seeing the other responses and wasn't thinking about containing the worksheet check in another function. So yes, in that sense it doesn't matter.

However I maintain that it is bad practice and is discouraged even in programming languages that have better error capture routines. Even when it may be more convenient.

Added to the fact that you are looping through a collection that is naturally limited to a small size and the difference in time would be measured is less than milliseconds.