If you’re working with a large worksheet in Excel, it usually helps if you freeze the cells at the top and/or the left side of the sheet. That way, your headings are always visible, along with other key information that you’ve put at the top of the sheet. You can freeze each sheet individually, or use this macro to freeze all worksheets at once.
What Gets Frozen?
When I hear “Frozen”, I think of the Disney movie, and the song, “Let It Go!” pops into my head. If you’re not familiar with that song, lucky you! My granddaughter loved that movie, and had a birthday cake with Elsa on it, a couple of years ago (see the picture at the end of this post). Anyway, in that movie, everything got frozen.
In an Excel worksheet, the cells(s) that you have selected will control what gets frozen. Here’s a chart, with all the options that I can think of. If I missed something, please let me know.
Selection | Frozen Area |
---|---|
Cell A1 | All cells above and to the left of center |
Other cell in column A | All rows above that cell |
Other cell in row 1 | All columns to the left of that cell |
Other cell | All cells above and to the left of the selected cell |
Row 1 | All cells above and to the left of center |
Any other row | All rows above that row |
Column A | All cells above and to the left of center |
Any other column | All columns to the left of that column |
Freeze Worksheets Manually
If you just want to freeze one sheet, you can do it manually.
- First, select a cell, row or column, below and to the right of the area that you want frozen. See details in the chart above.
- On the Excel Ribbon, click the View tab
- Click the Freeze Panes command
- Click Freeze Panes, to freeze at the selected location – OR, choose a command to freeze the first row or first column
Freeze All Worksheets Macro
If you have lots of sheets in a workbook, and want to freeze all of them at the same spot, you can use this Freeze All Worksheets macro.
- Before you run the macro, select the cell(s) to use as the freeze location (se the chart above).
- When the macro runs, you’ll see a confirmation message, asking if you want to freeze at the current selection.
- Click Yes, to go ahead, and click No to cancel the macro.
The Freeze All Worksheets Code
Copy this macro into a regular code module, then select cell(s) on any worksheet, and run the macro to freeze all the sheets in the active workbook.
Sub FreezeAllSheets() 'www.contextures.com Dim wsA As Worksheet Dim ws As Worksheet Dim wbA As Workbook Dim strSel As String Dim lRsp As Long On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsA = ActiveSheet strSel = Selection.Address lRsp = MsgBox("Freeze all sheets at current selection?", _ vbQuestion + vbYesNo + vbDefaultButton1, "Freeze Sheets?") If lRsp = vbYes Then Application.ScreenUpdating = False For Each ws In wbA.Worksheets ws.Activate Range(strSel).Select ActiveWindow.FreezePanes = True Next ws wsA.Activate Else 'do nothing End If exitHandler: Application.ScreenUpdating = True Exit Sub errHandler: MsgBox "Could not freeze all sheets" Resume exitHandler End Sub
Get the Sample Workbook
To get the sample workbook with the Freeze All Worksheets macro, go to the Excel Worksheet Freeze and Zoom Macros page on my Contextures website.
You can test the macro in that workbook, before adding it to your own files. Remember to enable macros when you open the workbook.
The sample workbook also has two other macros:
- Unfreeze All Worksheets
- Zoom All Worksheets (enter the zoom level that you want(
The Frozen Birthday Cake
Here’s my granddaughter’s Frozen birthday cake. Today is her birthday, and she chose a plain chocolate cake for this year’s celebration. She must be growing up!
____________________
When I am trying to freeze 5000 sheets this macro is not working.
Showing an error message such as “Could not freeze all sheets”
What to do or any other VBA.