Delete Sheets if exists and create new sheet

melewie

Board Regular
Joined
Nov 21, 2008
Messages
185
Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Worksheets.Add.Name = "Missing Data"<o:p></o:p>
<o:p></o:p>
If error delete the bloody thing and create a new one!!<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Worksheets.Add.Name = "Missing Data"<o:p></o:p>
<o:p></o:p>
If error call answer<o:p></o:p>
<o:p></o:p>
Any help would be greatly appreciated
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this function

Code:
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

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

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
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

Well-known Member
Joined
Apr 17, 2008
Messages
545
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.

Code:
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

Banned
Joined
Feb 8, 2003
Messages
5,378
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.

Code:
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

Well-known Member
Joined
Apr 17, 2008
Messages
545
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

Banned
Joined
Feb 8, 2003
Messages
5,378
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

Well-known Member
Joined
Apr 17, 2008
Messages
545
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.
 

Forum statistics

Threads
1,171,885
Messages
5,878,041
Members
433,313
Latest member
Excelnoob69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top