delete all sheets except the first 2 and the last X amount of sheets

[miming1986]

How do I delete all sheets except the first 2 sheets and the last X amount of sheets? I also want to have a pop-up message asking the user how many last sheets they don't want to get deleted.

Note: 2nd sheet is hidden - so i don't want to delete the first visible, the 2nd (which is hidden) and last X amount of sheets

Thanks so much in advance!


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Derek McGill]

What quantity is "X" or how is it worked out ?

 

[shrivallabha]

See if attached file will help you. I have created a userform.
- Click on the button to open the form.
- Select the workbook name from drop down
- It will show you worksheets name in the list.
- You can delete single worksheet by double clicking in the list or to delete multiple sheets just select the sheets you need to delete and press the button.

Note: Form is not very rigorously tested. I was just plain bored so made it. It may not be what you want.

 

[miming1986]

userform can be useful but i want a command button where it will delete all sheets except the first two and the last x amount of sheets. The first two sheets are very vital (Dashboard - visible and Master - hidden) and the rest sheets vary (daily tracker). At the end of the month, these sheets need to be deleted, however, some shouldn't be because they are part of the current month. All my sheets' name except the first two are in "mmdd" format.

 

[shrivallabha]

You can do this by using code like below:

Public Sub DelShts()
Dim lngLastShtCount As Long, i As Long
lngLastShtCount = Application.InputBox("Provide sheet count to be skipped:", "Do Not Delete", , , , , , 1)
Application.DisplayAlerts = False
For i = ThisWorkbook.Sheets.Count - lngLastShtCount To 3 Step -1
    ThisWorkbook.Sheets(i).Delete
Next
Application.ScreenUpdating = True
End Sub

 

[miming1986]

thanks so much shrivallabha! it worked, however, if i click cancel on the input box, the execution doesn't stop and the whole sheets get deleted except the first two and when i click ok without any input, i want the error message to be personalized like "please input the last amount of sheets to be skipped"

 

[shrivallabha]

Test below code and see if this is what you want.

Public Sub DelShts()
Dim lngLastShtCount As Long, i As Long
Dim vbMsgRslt As VbMsgBoxResult
UserInput:
lngLastShtCount = Application.InputBox("Provide sheet count to be skipped:", "Do Not Delete", , , , , , 1)
If lngLastShtCount = 0 Then
    vbMsgRslt = MsgBox("Press Yes: To Delete All Sheets Except First Two" & vbCrLf & _
                      "Press No : To go back and input number of sheets again" & vbCrLf & _
                      "Press Cancel : To cancel deletion", vbYesNoCancel)
    Select Case vbMsgRslt
    Case vbYes
        '\\ Do nothing
    Case vbNo
        GoTo UserInput
    Case vbCancel
        Exit Sub
    End Select
End If
Application.DisplayAlerts = False
For i = ThisWorkbook.Sheets.Count - lngLastShtCount To 3 Step -1
    ThisWorkbook.Sheets(i).Delete
Next
Application.ScreenUpdating = True
End Sub

 

[miming1986]

Thanks much! But i have a question, is it not possible to terminate the input box after clicking cancel? Why do we have to have another box to cancel it? And for clicking ok with an empty string, can we have a personalized message rather than the default "We found a problem with this formula..." or we can just terminate it.
Sorry my VBA knowledge is very minimal.

 

[shrivallabha]

Thanks much! But i have a question, is it not possible to terminate the input box after clicking cancel? Why do we have to have another box to cancel it? And for clicking ok with an empty string, can we have a personalized message rather than the default "We found a problem with this formula..." or we can just terminate it.
Sorry my VBA knowledge is very minimal.

Let's just say this is the easy path The result returned for cancel and 0 input are the same and I did not dig further.