Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

[Melimob]

HI, I have tried both below options to either show all data or remove filters in preparation for the workbook to be reused but neither work.

On top of this I would like to clear contents in range A:W down to last entry on one sheet and a different range on another sheet.
Both are in tables but I want to leave the formulas in my helper columns so can't clear the contents of whole tables.

Option Explicit
Sub RemoveAllFilters()


Dim Sht As Worksheet


For Each Sht In Worksheets
    If Sht.AutoFilterMode = True Then
        Debug.Print Sht.Name
        Sht.AutoFilterMode = False
    End If
Next




End Sub

Sub ClearFiltersAllSheets()


WS_Count = ActiveWorkbook.Worksheets.Count


' Begin the loop to disable all filters
For i = 1 To WS_Count
    If ThisWorkbook.Sheets(i).FilterMode Then
        ThisWorkbook.Sheets(i).ShowAllData
    End If
Next i


End Sub

I would like to have a message pop up to say 'reset completed, move to step 2'
or something like this.

Many thanks in advance for any advice!

 

[Melimob]

Try

      For Each listobj In Ws.ListObjects
         listobj.AutoFilter.ShowAllData
         listobj.Sort.SortFields.Clear
       Next listobj

thanks still getting an error 'object variable or block not set'? on this row: listObj.AutoFilter.ShowAllData

Sub ResetFilters()
      Dim Ws As Worksheet
      Dim wb As Workbook
      Dim listObj As ListObject
       Set wb = ThisWorkbook
       'Set wb = ActiveWorkbook
       'This is if you place the macro in your personal wb to be able to reset the filters on any wb you're currently working on. Remove the set wb = thisworkbook if that's what you need
           For Each Ws In wb.Worksheets
              If Ws.FilterMode Then
              Ws.ShowAllData
              Else
              End If
     'This removes "normal" filters in the workbook - however, it doesn't remove table filters
       For Each listObj In Ws.ListObjects
         listObj.AutoFilter.ShowAllData
         listObj.Sort.SortFields.Clear
       Next listObj
       
            Next
'And this removes table filters. You need both aspects to make it work.
    End Sub

 

[Fluff]

Do you have headers on your tables?
If not try

      For Each listobj In Ws.ListObjects
         If Not listobj.AutoFilter Is Nothing Then
            listobj.AutoFilter.ShowAllData
            listobj.Sort.SortFields.Clear
         End If
       Next listobj