Worksheet_Deactivate Acting Strangely

[jtfish]

I can't figure this out for the life of me. I have the following code in the worksheet object for a sheet called "Data":

Private Sub Worksheet_Deactivate()

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

End Sub

This is supposed to clear all filters on the Data sheet as soon as the user leaves that sheet. The problem is, it does nothing to those filters and instead clears a filter on a different sheet every time. There are many sheets in the workbook and it doesn't matter which one I move to. One thing that is peculiar though, and I'm not sure if this is related, but I have another macro that unhides all sheets in the workbook and when that macro completes, the sheet that is erroneously being unfiltered is the one that is always active. It's not even the last sheet in the workbook. I apparently can't use "Worksheets("Data").Activate in the code above because then you're unable to leave the sheet. Also, I have a regular macro activated by a form control on the "Data" sheet that runs the same code above and that works fine, so I know the code works. Any ideas on how to resolve this?

 

[Candyman8019]

have you tried:

[I]If (Worksheets("Data").AutoFilterMode And Worksheets("Data").FilterMode) Or Worksheets("Data").FilterMode Then[/I]
Worksheets("Data")[I].ShowAllData[/I]

 

[Fluff]

Replace every instance of ActiveSheet with Me

 

[jtfish]

Fluff: Your suggestion gives me a "Invalid use of Me keyword" error.

Candyman: That worked! I did need to get rid of the 's and 's though. I'm not sure if that was just a font translation thing.

Thank you both very much for for responding to my question. This forum is amazing. I used it extensively even before joining.

 

[Fluff]

This should not give that error

Private Sub Worksheet_Deactivate()

If (Me.AutoFilterMode And Me.FilterMode) Or Me.FilterMode Then
Me.ShowAllData
End If

End Sub