Sometimes, you may want to delete all Worksheets except the active one, if the Workbook have many Worksheets, it seems very boring to do it manually. In these situations, you can use this simple macro.
Delete All Worksheets Except Active One
'---------------- Modules ---------------- Sub DeleteAllWorksheetsExceptActive() 'Step 1: Declare your variables Dim ws As Worksheet 'Step 2: Start looping through all worksheets For Each ws In ThisWorkbook.Worksheets 'Step 3: Check each worksheet name If ws.Name <> ThisWorkbook.ActiveSheet.Name Then 'Step 4: Turn off warnings and delete Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If 'Step 5: Loop to next worksheet Next ws End Sub
How This Macro Works
- In Step 1, the macro first declares an object called ws. This creates a memory container for each worksheet it loops through.
- In Step 2, the macro begins to loop, telling Excel it will evaluate all worksheets in this workbook.There is a difference between ThisWorkbook and ActiveWorkbook.
- The ThisWorkbook object refers to the workbook that the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, we don’t want to risk deleting sheets in other workbooks, so we use ThisWorkbook.
- Note that when you use ThisWorkbook in a macro instead of ActiveWorkbook, you can’t run the macro from the Personal Macro Workbook. This is because ThisWorkbook refers to the Personal Macro Workbook, not the workbook to which the macro should apply.
- In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.
- In Step 4, if the worksheet names are different with the active sheet name, the macro deletes the worksheet. We use the DisplayAlerts property turns off Excel’s warnings so you don’t have to confirm each delete.
- In Step 5, the macro loops back to get the next worksheet. After all sheets are evaluated, the macro ends.
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment
'------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?
The following steps teach you how to put VBA code into a Standard Module:
- Activate the Visual Basic Editor by pressing ALT + F11.
- Right-click the project/workbook name in the Project Window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA