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?

Solution

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

Comments

[CLockeWork]

What do you mean by clean workbook contents?

[Chris Allen]

Delete all values (i.e contents) and formatting of all sheets in the workbook

[Chris Allen]

thank you but the previous one is more practical, there is no need to configure for each sheet.

[Chris Allen]

Thsnks works like a charm. I've added also "Selection.ClearFormats" to clear also formatting in all sheets...