VBA Code to remove Autofilter

[nitro2481]

Hi guys I have seen this question asked before but not exactly like this.

I have multiple sheets and some of them need auto filter to stay in place.

My problem, just for example, I have sheet 1 that when I pick a certain drop down, information automatically goes into sheet 2
And when I change sheet 3, information automatically goes to sheet 4. At times I will use auto filter just to get a summary for a certain month for example, and if I forget to turn it off the next time I trigger a movement from sheet 1 to 2 or 3 to 4 it doesn’t go in to the next available line, it just goes randomly in the sorted list and overtypes a previous entry.

In English I’d like a code that says “on close if sheet 2 is visible remove auto filter and if sheet 4 is visible remove autofilter etc”

The reason for the visible is not all users have access to all sheets so if I do “sheet 2. select. clear all filters, I’ll get an error I’d guess.

Also if it’s possible where would I put the code. I currently have a private sub for workbook_beforeclose (cancel as Boolean) in “This workbook” module so my guess would Be in there

 

[Fluff]

One option would be to put this code in the sheet module for both sheet 2 & sheet 4

Private Sub Worksheet_Deactivate()
If Me.FilterMode Then Me.ShowAllData
End Sub

 

[nitro2481]

Cheers Fluff

That worked perfect. I notice that undoes the autofilter as soon as I leave the sheet, never thought of that option. Drawback would be if you are flicking between sheets and want to look at all things in June for example and make comparisons to another page you would have to keep resorting them each time but I cannot think of an example personally where I’d have to do that.

Thanks a mil for the help

 

[Fluff]

Glad to help & thanks for the feedback