Delete Unselected Sheets From Workbook

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:

delete unselected sheets

One thought on “Delete Unselected Sheets From Workbook

  1. Pingback: Excel Roundup 20151019 « Contextures Blog

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>