apply to all sheets in active workbook

[heathball]

I have this tiny issue where i am trying to apply the 2nd part of this code (auto filter & Freeze panes)
to all sheets in the active workbook.

Does anyone know what change is required to achieve that?

Sub filterfreezeallsheets()
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate


Rows("1:1").Select
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub

 

[kevin9999]

Normally, the method would look something like this:

Sub filterfreezeallsheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
   
    'Your code goes here

    Next ws
End Sub

 

[heathball]

Thanks Kevin. I have applied this code.
It goes through the process of appearing to apply to all sheets (its busy and moving like it should)
But it is only working on the active sheet?

Sub filterfreezeall()

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
   
    Rows("1:1").Select
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

    Next ws
End Sub

 

[kevin9999]

Because you're using With ActiveWindow - which means the code will only be applied to whatever window is active. The following will apply the autofilter to all sheets, but I can't quite figure out which cell you want to apply the freeze panes to ?!

Sub filterfreezeall()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .Rows("1:1").AutoFilter
        End With
    Next ws
End Sub

 

[heathball]

Because you're using With ActiveWindow - which means the code will only be applied to whatever window is active. The following will apply the autofilter to all sheets, but I can't quite figure out which cell you want to apply the freeze panes to ?!

Sub filterfreezeall()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .Rows("1:1").AutoFilter
        End With
    Next ws
End Sub

ok i understand.
how do i add "freeze top row" to all sheets?

 

[kevin9999]

Looks like you have to activate the sheet & use active window after all...

Option Explicit
Sub filterfreezeall()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        If Not ws.AutoFilterMode Then ws.Rows("1:1").AutoFilter
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
    Next ws
End Sub

 

[heathball]

wow. thats awesome. thanks.
it also works when some sheets have it applied, and some not (instead of reversing it on sheets where it has been applied) - which is great

with "option explicit" i'm reading about it-
I cannot find a clear reason why you chose to add it to this code.

"finding typing errors" or/and "force yourself to declare all the variables" is about all i can see so far.

 

[kevin9999]

Happy to help and thanks for the feedback

why you chose to add it to this code.

Just a habit that's good to get into. It can save you a lot of problems in the long run, and doesn't hurt to include it every time.