How to clear contents from all sheets except one using VBA?

To clear contents from all sheets in an Excel workbook except for one specific sheet using VBA, you can loop through all the sheets and check their names against the specified sheet you want to retain. Here is a step-by-step guide and a sample VBA code to achieve this:

Sub ClearContentsExceptOne()
    Dim ws As Worksheet
    Dim sheetToKeep As String

    ' Name of the sheet you want to keep
    sheetToKeep = "Sheet1" ' Change this to the name of the sheet you want to keep

    Application.ScreenUpdating = False ' Optional: Improve performance and prevent flickering

    ' Loop through all sheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the sheet is not the one to keep
        If ws.Name <> sheetToKeep Then
            ' Clear contents of the entire sheet
            ws.Cells.ClearContents
        End If
    Next ws

    Application.ScreenUpdating = True ' Re-enable screen updating

    MsgBox "Contents cleared from all sheets except " & sheetToKeep

End Sub

Open Excel and Press ALT + F11: This will open the Visual Basic for Applications (VBA) editor.
Insert a Module:

In the VBA editor, click on “Insert” in the menu.
Select “Module” to add a new module where you will write your VBA code.

Write the VBA Code:

Copy and paste the following code into the module window:

Customize the Code:

Change the value of `sheetToKeep` to the name of the sheet you want to exclude from clearing contents.

Run the Code:

You can run this code by pressing F5 while inside the VBA editor or by going back to Excel, pressing ALT + F8, selecting `ClearContentsExceptOne`, and clicking “Run”.

Important Notes:

Make sure the sheet you intend to keep is correctly named in the code.
This operation cannot be undone, so it’s a good idea to create a backup of your workbook before running the script.
The code clears only the contents (values and formulas) but not the formatting or other aspects of the sheets. If you need to clear formats as well, you can use `ws.Cells.Clear` instead of `ws.Cells.ClearContents`.

facebooklinkedin