VBA to Remove Filters When Closing Workbook

[DavidWF]

I have filters on 100+ columns on many of my worksheets and I frequently forget to remove the filter when moving to another sheet. Then, when I return to that sheet, I can easily not realise that a filter is already in place, leading to all sorts of problems.

I've been trying to automate removal of filters but it's not working properly. As a first step I currently have the following in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim WS As Worksheet
    
    For Each WS In Worksheets
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
    Next WS

ThisWorkbook.Save

End Sub

I just opened the workbook and 2 filters were still in place. How should I be coding this so that it works as intended? Ultimately I'd like coding that removes the filters every time I move to another sheet, but filter removal on closing the workbook would be OK as a first step.

 

[Michael M]

MAybe this....but make sure ALL sheets are Unprotected

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet
For Each WS In Worksheets
ws.activate       
   If ws.FilterMode Then ws.ShowAllData
Next WS
ThisWorkbook.Save
End Sub

 

[DavidWF]

Hi Michael

You can forget the MAybe - it worked like a charm!

I made one small addition as it was, understandably, opening the workbook at the last worksheet (which I rarely use) so I added in a line to select the sheet that I most commonly use, enabling the workbook to now open at that sheet. Of course the problem is that I'll now get lazy and won't bother to close any filters, happy in the knowledge that the coding will do it for me!

Thanks again for making my day.
David

 

[Michael M]

glad to help..