VBA Deleting Active Sheet

Question

I am trying to create a macro that deletes the active sheet without displaying the prompt. Which is what the code below does...This works great until the last sheet. I get the prompt no matter what. I do not want to delete the last sheet and at the same time, I don't want the error '1004' message to come up. Is there a way to change the code above to not delete my last sheet and not display the error message at the same time?

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

Answer 1

If the idea is to delete the ActiveSheet and only it, this is something that will work, until there is only 1 sheet in the workbook:

Sub DeleteActiveSheet()

    If ThisWorkbook.Worksheets.Count = 1 Then
        Exit Sub
    Else
        Application.DisplayAlerts = False
        ThisWorkbook.ActiveSheet.Delete
        Application.DisplayAlerts = True
    End If

End Sub

If the idea is to delete all worksheets, but the last, then follow this sequence:

• assign a worksheet variable wksToStay from the type Worksheet and set it to the last worksheet in the workbook;
• loop through all the Worksheets in the Workbook.Worksheets collection, starting from the last one;
• always perform a check, whether the worksheet to be deleted wksToDelete has the same name as the wksToStay;
• delete, if the name is not the same;
• it will delete all the worksheets, including the hidden and the very hidden ones;

---

Sub DeleteAllButLast()

    Dim wksToStay As Worksheet
    Dim wksToDelete As Worksheet
    Dim i As Long

    Set wksToStay = ThisWorkbook.Worksheets(Worksheets.Count)

    For i = Worksheets.Count To 1 Step -1
        Set wksToDelete = ThisWorkbook.Worksheets(i)
        If wksToDelete.Name <> wksToStay.Name Then
            Application.DisplayAlerts = False
            wksToDelete.Delete
            Application.DisplayAlerts = True
        End If
    Next

End Sub