Macro to Delete Every Sheet Except the Active One

This VBA macro deletes every worksheet in the workbook except the one you’re on. A second version asks for confirmation first.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Delete all sheets except the active one

Sub DeleteAllExceptActive()
    Dim i As Long
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        If Worksheets(i).Name <> ActiveSheet.Name Then Worksheets(i).Delete
    Next i
    Application.DisplayAlerts = True
End Sub

The loop counts down from the last sheet, so deleting one doesn’t make it skip the next.

With a confirmation message

Sub DeleteAllExceptActiveSafely()
    Dim i As Long
    If MsgBox("Delete every sheet except " & ActiveSheet.Name & "?", vbYesNo + vbExclamation) = vbNo Then Exit Sub
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        If Worksheets(i).Name <> ActiveSheet.Name Then Worksheets(i).Delete
    Next i
    Application.DisplayAlerts = True
End Sub

Things to know

  • There’s no Undo. Save a copy of the workbook first.
  • Formulas on the remaining sheet that referred to deleted sheets show #REF!. Convert them to values first if you need to keep the results.
  • Worksheets doesn’t include chart sheets. To delete those too, use Sheets instead of Worksheets.

Related macros