Bulk Freeze Panes Setter: Lock Header Rows Across Every Sheet at Once

TL;DR: This macro asks you which row (and optionally which column) to freeze, then applies freeze panes to every visible sheet in one pass. No more View → Freeze Panes per-tab for a 20-sheet workpackage. Row 0 means “remove freeze panes entirely.”

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook where every sheet (or most sheets) has header rows you want frozen

Limitations:

• Freeze panes are applied per sheet — this macro activates each visible sheet briefly to set the freeze point, then returns to your original sheet
• Hidden sheets are automatically skipped and reported in the final count
• Row 0 means “no row freeze” — the macro will remove all freeze panes if you enter 0 for both row and column
• The macro cannot freeze panes on protected sheets where Select locked cells is unchecked

#The Macro

Option Explicit

Sub BulkSetFreezePanes()
    ' ── Bulk Freeze Panes Setter ──────────────────────
    ' Sets freeze panes at the specified row and column
    ' on every visible sheet. Enter 0 for both to remove
    ' all freeze panes instead.
    ' ────────────────────────────────────────────────────

    Dim ws As Worksheet
    Dim startWS As Worksheet
    Dim freezeRow As Variant
    Dim freezeCol As Variant
    Dim fRow As Long
    Dim fCol As Long
    Dim count As Long
    Dim skip As Long

    Set startWS = ActiveSheet
    count = 0
    skip = 0

    ' ── Get user input ──────────────────────────────────
    freezeRow = InputBox("Freeze at which row?" & vbCrLf & _
        "1 = freeze top row only" & vbCrLf & _
        "2 = freeze first 2 rows" & vbCrLf & _
        "0 = no row freeze (remove freeze panes)", _
        "Bulk Freeze Panes", "1")
    If freezeRow = "" Then Exit Sub
    If Not IsNumeric(freezeRow) Then
        MsgBox "Please enter a number.", vbExclamation
        Exit Sub
    End If
    fRow = CLng(freezeRow)
    If fRow < 0 Then
        MsgBox "Row must be 0 or greater.", vbExclamation
        Exit Sub
    End If

    ' Only ask for column freeze if a row is being frozen
    If fRow > 0 Then
        freezeCol = InputBox("Freeze at which column?" & vbCrLf & _
            "1 = freeze first column only" & vbCrLf & _
            "0 = no column freeze", _
            "Bulk Freeze Panes — Column", "0")
        If freezeCol = "" Then Exit Sub
        If Not IsNumeric(freezeCol) Then
            MsgBox "Please enter a number.", vbExclamation
            Exit Sub
        End If
        fCol = CLng(freezeCol)
        If fCol < 0 Then
            MsgBox "Column must be 0 or greater.", vbExclamation
            Exit Sub
        End If
    Else
        fCol = 0
    End If

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    On Error GoTo CleanUp

    ' ── Apply freeze panes to every visible sheet ──────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Activate

            ' Remove existing freeze panes first
            If ActiveWindow.FreezePanes Then
                ActiveWindow.FreezePanes = False
            End If

            ' Apply new freeze panes if a row or column was specified
            If fRow > 0 Or fCol > 0 Then
                ws.Cells(fRow + 1, fCol + 1).Select
                ActiveWindow.FreezePanes = True
                count = count + 1
            Else
                ' fRow = 0 and fCol = 0 — just removed them
                count = count + 1
            End If
        Else
            skip = skip + 1
        End If
    Next ws

    ' ── Return to the original sheet ───────────────────
    startWS.Activate

    ' ── Report what happened ───────────────────────────
    If fRow = 0 And fCol = 0 Then
        MsgBox "Removed freeze panes from " & count & _
               " visible sheet(s)." & vbCrLf & _
               skip & " hidden sheet(s) skipped.", _
               vbInformation, "Done"
    Else
        Dim desc As String
        desc = "row " & fRow
        If fCol > 0 Then desc = desc & " and column " & fCol
        MsgBox "Applied freeze panes at " & desc & _
               " on " & count & " visible sheet(s)." & vbCrLf & _
               skip & " hidden sheet(s) skipped.", _
               vbInformation, "Done"
    End If

CleanUp:
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two InputBoxes, one decision

The first InputBox asks for the freeze row. It shows examples so you don’t need to think about how Excel’s freeze logic works — 1 means freeze the top row, 2 means freeze the first two rows, and so on. If you enter 0, the macro skips row freezing entirely and removes any existing freeze panes.

The second InputBox only appears if you entered a row number above 0. It asks for the freeze column the same way — 1 for the first column, 0 for none. This two-step flow avoids confusing the user with a column question when they just want to nuke all freeze panes.

#The cell-below-the-freeze-point trick

Excel’s ActiveWindow.FreezePanes = True freezes at the current selection, not at a specified coordinate. If you select cell C4 and run it, rows 1–3 and columns A–B freeze. So the macro selects the cell one row below and one column to the right of the freeze point:

ws.Cells(fRow + 1, fCol + 1).Select
ActiveWindow.FreezePanes = True

Enter row 2, column 1 → selects Cells(3, 2) = B3 → freezes rows 1–2 and column A. Enter row 1, column 0 → selects Cells(2, 1) = A2 → freezes row 1 only. It’s the same manual operation you’d do with the mouse, just automated.

#Clear first, then apply

Every sheet gets its existing freeze panes cleared before the new ones are applied. This avoids a scenario where a sheet at the wrong freeze point stays that way because FreezePanes = True doesn’t move an existing freeze — it returns False if you try to set it without first clearing. The two-step pattern is:

If ActiveWindow.FreezePanes Then
    ActiveWindow.FreezePanes = False   ' clear whatever's there
End If
ws.Cells(fRow + 1, fCol + 1).Select
ActiveWindow.FreezePanes = True        ' set the new freeze

#ScreenUpdating hides the flicker

Activating every sheet one by one would normally flash through every tab like a slideshow. Application.ScreenUpdating = False suppresses the visual update, and the original active sheet is restored at the end with startWS.Activate. To the user, nothing appears to have changed except the freeze panes.

#Cleanup restores state no matter what

The On Error GoTo CleanUp pattern ensures ScreenUpdating always gets turned back on, even if the macro hits a protected sheet or another error mid-loop. Without this, your Excel window would go blank and unresponsive — and the only fix is to run Application.ScreenUpdating = True in the Immediate Window.

#Why a message box at the end

The MsgBox tells you two things: how many sheets were changed (so you can verify it matches expectations) and how many were skipped because they were hidden (so you know something was intentionally left out). If the count doesn’t match your sheet count, you know to investigate.

#Adapt It