How to delete all sheets except one in a workbook using VBA?

To delete all sheets except one in an Excel workbook using VBA, you can use the following steps. This script will keep a specified sheet while deleting the others:

Sub DeleteAllExceptOne()
    Dim ws As Worksheet
    Dim SheetToKeep As String
    
    ' Specify the name of the sheet you want to keep
    SheetToKeep = "Sheet1" ' Change "Sheet1" to the name of the sheet you want to keep
    
    Application.DisplayAlerts = False ' Disable alerts to delete sheets without confirmation
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet is not the one to keep
        If ws.Name <> SheetToKeep Then
            ws.Delete ' Delete the worksheet
        End If
    Next ws
    
    Application.DisplayAlerts = True ' Re-enable alerts
End Sub

Open your Excel workbook.
Press `ALT` + `F11` to open the Visual Basic for Applications (VBA) editor.
Go to `Insert > Module` to create a new module.
Copy and paste the following VBA code into the module window:
Change the `SheetToKeep` variable to match the name of the sheet you do not want to delete.
Close the VBA editor.
Run the macro by pressing `ALT` + `F8`, selecting `DeleteAllExceptOne`, and clicking `Run`.

This script disables alerts temporarily to avoid confirmation prompts when deleting sheets. It loops through each worksheet in the workbook, checks if it matches the specified sheet to keep, and deletes it if it does not match. Make sure to back up your workbook before running the script, as this action cannot be undone.

facebooklinkedin