Vba Clearing Datarange columns across multiple sheets

[Melimob]

Hi

I have looked at this thread which was helpful but doesn't give me quite what I need..

https://www.mrexcel.com/forum/excel-questions/1002856-vba-clearing-specific-table-columns.html
I also found this useful and started to write the code but realised it was referencing rows and I need specific columns.
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Basically, I need to clear out all the databody ranges across all my tables in the workbook EXCEPT leaving the formulas?

Is there an easy way to do this or do I have to specify which columns to leave alone for each table?

Many thanks
Meli

 

[Melimob]

I've found the answer which works great except that it's also clearing the tables from my lkup sheet which I need to keep.

https://www.mrexcel.com/forum/excel...-all-data-except-formulas-table-headings.html

I tried to recreate the tables as at first it was only deleting the first table but now all on that sheet.

So I need something that will run this code for All Sheets EXcept sheet named 'lkups'.

Sub ClearAllButFormulas()
    
    RemoveFiltersUnhide
        
    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

many thanks

 

[Melimob]

ok I found the answer however it still was looking at my LKUPs sheet but I recreated all the tables and so it gets rid of one I don't need. Would like to know how to stop it reading this sheet tho?

Sub ClearAllButFormulas()
    
    RemoveFiltersUnhide


  Dim ws As Worksheet
  Dim wks As Worksheet
  Dim Tbl As ListObject


For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "LKUPs" Then
  
    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
        
  End If


Next ws


End Sub