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?

 

[dsrt16]

Fluff

I added a button and assigned it the macro. It worked again. Then I reformatted cells outside of the tables, changing their background color, and when I tried the macro again, it did not work. It said error: no cells were found. So I undid all the formatting it, and it worked again.

But then I added a text box label to the sheet, and when I ran the macro, I got the error again. I deleted the text box labels I had added to see if they were the problem, but the macro is still giving me that error.

I don't understand. I only changed cells outside of the tables, so I am not sure why that should affect the macro. When I reversed my changes the first time, the macro worked again. When I reversed the most recent changes, the macro still didn't work. Any ideas?

 

[dsrt16]

It highlights this line when I debug:

Tbl.DataBodyRange.SpecialCells _
             (xlCellTypeConstants, 23).ClearContents

I just don't understand why it worked at first, but not now. Nothing inside the tables changed.

 

[dsrt16]

So I opened an older version of the workbook, added the macro, tested it, and it worked. Then as a test, I pressed the clear all button again, and it went into debug mode (since there was no data to clear), but I thought the macro was set to ignore all errors in case all that was left was formulas. I then added the data and tried again, but it didn't work since it was an already broken macro in debug mode. I stepped out of it, but it still didn't work. So even though there is now data to clear, the macro won't work after it had an error before.

 

[Fluff]

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
        For Each Tbl In wks.ListObjects
            On Error Resume Next
            Tbl.DataBodyRange.SpecialCells _
               (xlCellTypeConstants, 23).ClearContents
            On Error GoTo 0
        Next Tbl
    Next
    Set wks = Nothing
End Sub

 

[stroffso]

This is exactly what I am looking for thank you

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

 

[Fluff]

Glad it worked for you and thanks for the feedback