Delete Unselected Sheets From Workbook

My time is short and so is my code. As a sort of companion piece to the recent post on removing filters from all but the selected columns, here’s some VBA to delete unselected sheets from a workbook. It’s a type of action I find useful. Often before I send a workbook to somebody I want to delete all but one or two sheets. Sure, I could select them, right click, choose delete and answer the confirmation prompt, but I’m not a young man. My time is precious, and since deleting sheets kills the Undo stack anyways, why not do it with code?

I could use two nested loops to test whether each sheet is one of the selected sheets, but I like this better:

Sub DeleteUnselectedSheets()
Dim SelectedSheetsCount As Long

If ActiveWorkbook Is Nothing Then
MsgBox "No active workbook."
Exit Sub
End If

SelectedSheetsCount = ActiveWindow.SelectedSheets.Count
If MsgBox("Really?", vbYesNo, "Delete Unselected Sheets") = vbNo Then
Exit Sub
End If

ActiveWindow.SelectedSheets.Move before:=ActiveWorkbook.Sheets(1)
Do Until ActiveWorkbook.Sheets.Count = SelectedSheetsCount
Application.DisplayAlerts = False
ActiveWorkbook.Sheets(SelectedSheetsCount + 1).Delete
Application.DisplayAlerts = True
Loop
End Sub

You’ll notice that I chickened out and put a confirmation dialog in there. Common sense got the better of me.

The Move command keeps the SelectedSheets in the same order and moves them to the leftmost positions. At that point only the first one is selected, which is why we saved the number of selected sheets at the beginning. The Do Until loop then deletes all the sheets with a higher index than the count of originally selected sheets.

I tested it on charts and hidden sheets and it worked fine:

One thought on “Delete Unselected Sheets From Workbook”