Selected Answer
Hello Arsil,
The AutoFilter method is a toggle. Every time it is called it will change to the opposite of what it was. To avoid surprises use the AutoFilterMode to determine the current status.
Sub AddFilter_on_allWorksheet()
' 28 May 2018
Dim Sh As Worksheet
Dim Rng As Range
Application.ScreenUpdating = False ' stop screen flicker
For Each Sh In Worksheets
With Sh
If .AutoFilterMode Then .AutoFilterMode = False
Set Rng = Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))
Rng.AutoFilter
.Activate
.Range("J4").Select
End With
With ActiveWindow
If .FreezePanes Then .FreezePanes = False
.FreezePanes = True
End With
Next Sh
Application.ScreenUpdating = True
End Sub
Your combinatilon of setting AutoFilter and FreezePanes is an unhappy one, at least if you want to do it on all the sheets in one go. AutoFilter doesn't require selection of the sheet whereas FreezePanes does. I have added code to suppress ScreenUpdating while flicking through all the sheets (and reduced the number of columns for which the filter is set).
Actually, I think it is the idea to do all the sheets at once which stifles the smiles. That makes sense only if you want to look at several sheets at once, and even then I would advise to run the code on the Activate event just for the sheet you are really looking at. It would be shorter, fully automatic, and customisable for each sheet (if need should arise for that in the future).
