Code to Reset Filter When Opening or Closing a Workbook

[cwmlpn]

I have searched the forum and could not find a resolution that works.
I am a newby when it comes to working with VBA and Macros so please excuse my ignorance.
64-bit operating system with Excel 2016 32-bit

I have a workbook with 3 worksheets. Sheet one contains a table with filters turned on. Multiple people will be using this worksheet with filters so I am needing a code that will automatically reset the filters upon opening or closing. (I don't have a preference for when this reset occurs, open or close, just whichever works the best)

I am not sure if it is relevant, but 6 out of 7 of the columns in the table are set up as a data validation drop down list and I have a pivot table on sheet 3.

Thanks in advance for your help!
cwmlpn

 

[mrshl9898]

Try this in 'ThisWorkbook'

Private Sub Workbook_Open()
For Each Sheet In ThisWorkbook.Sheets
Sheet.Activate
On Error Resume Next
If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If
Next Sheet
End Sub

 

[cwmlpn]

Thank You. I tried your suggestion and it does not seem to be working. I do not get an error, however when I filter, save then open again, the filters are still applied.
The one thing different I did notice however, is that now when I open my workbook, it opens to sheet 3 instead of sheet 1 like it had previously.
Like I said, I am just now learning to use the advance features like VBA and Macros, so I may be missing a step somewhere.

 

[cwmlpn]

I found a code that worked! Thanks for your help!
Here is the code that worked for me. It works at the point of saving the file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
For Each wks In Worksheets
If wks.FilterMode = True Then
wks.ShowAllData
End If
Next wks
End Sub