Freeze Top Row on All Sheets

What it does

Excel has Freeze Panes, but it only applies to the active sheet. On a 30-tab workbook, if you want every tab to have the same frozen header, you click each tab, click View > Freeze Panes > Freeze Top Row, and repeat 30 times. The macro does it once. Select any tab, run the tool, and Freeze Panes is applied at row 2 of every visible sheet in the workbook. If a sheet already has different freeze panes set, they're reset before the new one is applied so the result is consistent across the file.

Real-world example

Picture a regional sales manager at a distribution company who maintains a master pipeline workbook with one tab per sales rep. There are 22 reps, so 22 tabs. The first row of each tab has the column headers: account name, opportunity stage, expected close, deal size. When reviewing pipeline with each rep weekly, she scrolls down their tab to see the full opportunity list. Without Freeze Panes the headers disappear off the top of the screen and she has to remember which column is which.

Her current routine: every time the workbook is restructured (about quarterly), walk through all 22 tabs and re-apply Freeze Panes to row 2. About 3 minutes per workbook restructure, with two or three tabs accidentally skipped because her attention drifted. She finds the missing freezes weeks later when a rep complains the headers don't stick.

With this macro she opens the workbook, runs the tool, and Freeze Panes applies to all 22 tabs in 1 second. The popup confirms "Froze the top row on 22 visible sheet(s)." Every weekly review goes smoothly afterward because every tab behaves the same way.

On a 22-tab quarterly workbook restructure, expect about 3 minutes saved per pass. Per year that's a small win in time but a meaningful win in consistency: every tab works the same way, so reps don't ask "why don't the headers stick on my tab".

How to use

First time? See How to run our tools for the 30-second setup.

1. Open the workbook. The macro operates on the active workbook's visible sheets.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick FreezeTopRowAllSheets, click Run.
3. The macro applies Freeze Panes at row 2 to every visible sheet and reports the count.

FAQ

Does it touch hidden sheets?

No. The macro skips any sheet where Visible <> xlSheetVisible, so hidden and very-hidden sheets are left alone. If you want hidden sheets frozen too, unhide them, run the macro, then re-hide.

What if a sheet already has different Freeze Panes set?

The existing freeze gets reset before the new one is applied. So a sheet that had columns A and B frozen along with the header row ends up with just the header row frozen after the macro runs. This is intentional: the macro's job is to apply a consistent freeze across the workbook. If you want sheet-specific freeze settings, this isn't the right tool.

Can it freeze multiple rows or a column?

No, the macro freezes exactly one row (row 2 sets the freeze, so row 1 stays visible). To freeze multiple rows or a column too, edit the .bas source: change ws.Range("A2").Select to ws.Range("B3").Select for "freeze top 2 rows and column A", for example. Freeze Panes always uses the top-left of the active selection as the freeze split.

Does it work on Mac Excel?

Yes. The macro uses ActiveWindow.FreezePanes, which works on both Windows and Mac Excel 2016 and later. The Application.ScreenUpdating = False toggle keeps the screen flicker-free during the multi-tab loop.

Can I undo it?

Ctrl/Cmd + Z does NOT undo Freeze Panes settings reliably across all Excel versions. To unfreeze, run View > Freeze Panes > Unfreeze Panes manually on each tab, or write a quick "UnfreezeAll" companion macro by changing one line of the .bas source. The macro runs entirely offline. Your workbook never leaves your machine.