Macro for cleaning all sheets in a workbook (VBA – Excel 2010)

macrosmicrosoft-excel-2010vba

I have a macro file with 5 sheets and I would like to add a VBA command button into the same file to clean all workbook contents with a single click. Does anyone know how to do it by using VBA on excel 2010?

Best Answer

Cleraing all sheets in a workbook:

Sub ClearAll()

    Set wbook = ActiveWorkbook

    For Each sht In wbook.Worksheets
       sht.Activate
       sht.Cells.Select
       Selection.ClearContents
       Selection.ClearFormats ' edit: clear formats too
       sht.Cells(1, 1).Select ' edit: select the first cell to cancel selection of the whole sheet
    Next sht


End Sub

Edit1: see source

Deleting them instead of cleaning:

Sub DeleteAll()

    bAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False

    Set wbook = ActiveWorkbook

    wbook.Sheets(1).Activate

    For Each sht In wbook.Worksheets
        If sht.Name = wbook.ActiveSheet.Name Then ' we don't delete the active sheet but just its conntet
            sht.Cells.Select
            Selection.ClearContents
            Selection.ClearFormats
            sht.Cells(1, 1).Select
        Else
            sht.Activate
            ActiveWindow.SelectedSheets.delete
            wbook.Sheets(1).Activate
        End If
    Next sht

    wbook.ActiveSheet.Name = "Sheet1" ' we rename the last remaining sheet to the default name

    Application.DisplayAlerts = bAlerts

End Sub
Related Question