Deleting Worksheet Stops Script

[Tpipco]

My Script calls to delete an existing worksheet, regenerate a new worksheet under the same name and move forward with populating the new sheet with data.

However, once the existing worksheet has been deleted, the script does not continue.

When stepping through the script, it completes restoration of the Application.DisplayAlerts = True, but then returns to the top of the script without continuing.


For Each ws In ThisWorkbook.Worksheets
If ws.Name = x Then
Application.DisplayAlerts = False
Application.AutomationSecurity = msoAutomationSecurityLow
ws.Delete
Application.DisplayAlerts = True
GoTo 1
End If
Next ws


Thank you for your assistance.

[SamT]

My Script calls to delete an existing worksheet, regenerate a new worksheet under the same name and move forward with populating the new sheet with data
NOPE! it deletes a worksheet named the same as the contents of the variable x. Then the code starts running again at line #1

[Tpipco]

Thank you for viewing my Post, during the interim, I did come up with a more appropriate method for my application.
I wanted to share.... just in case it may assist others.

If the Worksheet Exists, Rather than Deleting the sheet , I Cleared it and Re-populated it.
If it does not exist... then it is created and the script continues.


Private Sub Button1_Click()
Dim x As String
x = "sheetname"
On Error GoTo 1
Sheets(x).Select
Sheets(x).Cells.Select
Selection.Delete
Sheets(x).Range("a1").Select
GoTo 2

1
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = x
GoTo 2

2
"Remainder of Script"
End Sub

[p45cal]

To avoid the spaghetti code always associated with GoTos why not something like this:

Private Sub Button1_Click()
Dim x As String, y As Worksheet

x = "sheetname"
On Error Resume Next 'this just means that the code will move onto the next line of code when an error is thrown. Use this when you think it likely that a line of code might throw an error.
Application.DisplayAlerts = False
Sheets(x).Delete 'will delete the sheet if it exists, will throw an error if not but will move on to the next line anyway
Application.DisplayAlerts = True
On Error GoTo 0 'restore normal error behaviour
'at this stage Sheets("sheetname") definitely doesn't exist.
Set y = Sheets.Add(after:=ActiveSheet) 'so add it
y.Name = x 'rename it
'you can refer to the new sheet using variable y
'"Remainder of Script"

End Sub

[snb]

Please remove the code in #3; it will confuse visitors.


Sub M_snb()
c00="example"
on error resume next

sheets.add( , sheets(sheets.count)).name=c00
sheets(c00).cells.clearcontents

on error goto 0
End sub

avoid:
- Select
- Activate
- GoTo
- merged cells
- 'Call'
- disabling commandbars or commandbaritems
- an empty cell A1

[Aussiebear]

Please remove the code in #3; it will confuse visitors.

No, the post will remain as an example of code that can be improved as per p45cal's example

[Tpipco]

I Thank Everyone for your Assistance !!