Hi all,

I was using the following code to check for filters on a sheet of data and then show all data.
If ThisWorkbook.Sheets(1).FilterMode Then
        ThisWorkbook.Sheets(1).Unprotect Password:=xxxx
        ThisWorkbook.Sheets(1).ShowAllData
        ThisWorkbook.Sheets(1).Protect Password:=xxxx, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingRows:=True, _
            AllowInsertingHyperlinks:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True
        ActiveSheet.EnableSelection = xlUnlockedCells
End If
It was decided to make the data on the sheet into a 'Table' and the above code does not work so I tried the following after recording a macro:
    ThisWorkbook.Worksheets(1).ListObjects("Table2").Sort.SortFields. _
        Clear
    ThisWorkbook.Worksheets(1).ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[[#All],[Store Code]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With ThisWorkbook.Worksheets(1).ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
The problem is if a filter is applied that shows a partial of the table, the above code does not undo that type of filter. I have searched vigorously and can not find an answer.

I have tried this line of code but it removes the drop down filters from the header row and I found that it also places the little drop down arrows in the selected row when they do not exist.

ThisWorkbook.Sheets("All Brands").ListObjects("Table2").Range.AutoFilter
The idea is on Workbook_Close() to run a filter so that the data is saved in the exact same form every time, so the next person opening it will see all the data sorted by a particular column.

Thanks,

TV