Freeze rows via vba, without any activation of individual sheets

Question

Is there any way to freeze the top x rows of an arbitrary amount of worksheets without using select, or activating every sheet?

This answer suggests using

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

, and recording a macro suggests using Rows("4:4").Select: ActiveWindow.FreezePanes = True, but I would prefer a solution which didn't depend on activating every sheet in my workbook.

I.e. something like

Sub FreezeTopXPanes(x As Long)
  Dim ws As Worksheet

  For Each ws in ThisWorkbook.Worksheets
    ' Freeze top x rows
  Next ws
End Sub

Is there any way to do this, or am I stuck with the solutions in the other question?

I don't think it's possible without activationg the various sheets. The documentation states that `FreezePanes` requires a `Window` object, not a `Sheet`. Also `FreezePanes` uses the position of the `ActiveCell` to find out how many rows and columns are to be frozen. — iDevlop, Oct 02 '17 at 08:42
I should maybe have quoted a bit more of the linked answer, and double-checked that I'd linked to the right one, as that one at least contains a method not using activecell / select. I've updated the question to include the pertinent information now, but as you can see, this solution depends on using activewindow too. @PatrickHonorez — eirikdaude, Oct 02 '17 at 08:50

score 1 · Answer 1 · answered Oct 02 '17 at 08:55

This is the issue. As @Patrick has mentioned this is rather impossible as freeze panes method works only with activewindow. See more details here.

I think the best way is to use separate procedure, for all sheets at a time or for certain one:

Sub Freeze_wsh() 
Dim Ws As Worksheet
Application.ScreenUpdating = False
For Each Ws In Application.ActiveWorkbook.Worksheets
    Ws.Activate
    With Application.ActiveWindow
        .FreezePanes = True
        .ScrollRow = 1 
        .ScrollColumn = 1 
    End With
Next
Application.ScreenUpdating = True
End Sub

Or you can add a variable for referencing certain sheet

Sub Freeze_wsh(x as Integer) 
Dim Ws As Worksheet
Application.ScreenUpdating = False
set Ws = ActiveWorkbook.Worksheets(x)
    Ws.Activate
    With Application.ActiveWindow
        .FreezePanes = True
        .ScrollRow = 1 
        .ScrollColumn = 1 
    End With
Next
Application.ScreenUpdating = True
End Sub

Freeze rows via vba, without any activation of individual sheets

1 Answers1