Macro to delete all data except formulas and table headings

[dsrt16]

I created a clear all button on a sheet that needs to be cleared at the end of the year, ready for use at the start of the next year. The macro worked, but it also cleared out the table headings. (the names of each column)

This was the macro I used:

Sub ClearAllButFormulas()
    Dim wks As Worksheet

    For Each wks In Worksheets
        'ignore errors in case there is only formulas
        On Error Resume Next
        wks.Cells.SpecialCells _
          (xlCellTypeConstants, 23).ClearContents
        On Error GoTo 0
    Next
    Set wks = Nothing
End Sub

What do I need to add to it to prevent it from deleting the table headings?

 

[Fluff]

How about

Sub ClearAllButFormulas()
    Dim wks As Worksheet

    For Each wks In Worksheets
        'ignore errors in case there is only formulas
        On Error Resume Next
        wks.UsedRange.Offset(1).SpecialCells _
          (xlCellTypeConstants, 23).ClearContents
        On Error GoTo 0
    Next
    Set wks = Nothing
End Sub

 

[dsrt16]

I tried that. It did the same thing: cleared data and table headings, changing them to column 1, column 2, column 3, etc. rather than the names I had entered in.

 

[Fluff]

What row are your heading on?

 

[Fluff]

Change of plan, If you have 1 table per sheet try

Sub ClearAllButFormulas()
    Dim wks As Worksheet
    Dim Tbl As ListObject

    For Each wks In Worksheets
        'ignore errors in case there is only formulas
        On Error Resume Next
        Set Tbl = wks.ListObjects(1)
        Tbl.DataBodyRange.SpecialCells _
          (xlCellTypeConstants, 23).ClearContents
        On Error GoTo 0
    Next
    Set wks = Nothing
End Sub

 

[dsrt16]

I didn't try that macro because I have two tables on each sheet.

Headings are on row 3 and row 27.

 

[Rick Rothstein]

I didn't try that macro because I have two tables on each sheet.

Headings are on row 3 and row 27.

Are they real Excel Table objects or just normal ranges of cells on a sheet that you think of as being a table? If just normal ranges, what are the column extents of each table?

 

[Fluff]

Ok try this

Sub ClearAllButFormulas()
    Dim wks As Worksheet
    Dim Tbl As ListObject

    For Each wks In Worksheets
        'ignore errors in case there is only formulas
        On Error Resume Next
        For Each Tbl In wks.ListObjects
           Tbl.DataBodyRange.SpecialCells _
             (xlCellTypeConstants, 23).ClearContents
           On Error GoTo 0
        Next Tbl
    Next
    Set wks = Nothing
End Sub

 

[dsrt16]

That worked perfectly. Thanks!

 

[dsrt16]

Are they real Excel Table objects or just normal ranges of cells on a sheet that you think of as being a table? If just normal ranges, what are the column extents of each table?

Yes, they are real table objects. Fluff's code worked.