Freeze All Worksheets Macro

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 panes manually

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.

Freeze All Worksheets 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!

frozencake

____________________

One thought on “Freeze All Worksheets Macro”

  1. 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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.