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

Question

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?

Answer 1

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

Answer 2

Simple as below. Change the Sheet1, Sheet2 to the names of your Sheets and add as many of those 3 lines as you need. I assume you know how to link create a button in excel and link to a Macro.

Sub ClearSheets()
    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents

    Sheets("Sheet2").Select
    Cells.Select
    Selection.ClearContents
End Sub