Goal: Enable Excel users to freeze panes in the same position, across all worksheets in an active workbook.

This presentation will walk you through you a macro-based solution to freeze panes in all worksheets in a workbook. As most Excel users know, Excel will only permit a user to freeze panes in one worksheet at a time.

Note, Excel will not permit the selection of multiple worksheet to freeze panes simultaneously. Therefore we need to go to Visual Basis for Applications and compose a macro.

Writing the freeze panes worksheets macro

It is advisable to notate the macro, just in case we need to review or edit the macro at a later date. We will call this macro, freezePanesSheets

Firstly we need to disable the Excel properties before the macro runs. Now we need to declare the following object variables for the macro.

These object variables will be crucial in the proper operation of the macro across all worksheets. Then we need to stipulate the location of some object variables – startSheet and startCell

We are now ready to compose our loop code.

This code will allow us to cycle through all the worksheets in the workbook. We will then be able to freeze the panes in the same location for each worksheet.

This line will move the macro to the next worksheet

Once the macro has moved through all the worksheets, we want to return the cursor to the initial worksheet and cell address “A1”.

The macro has finalized the freezing of panes across all sheets. We can now re-enable the following Excel properties.

Running the macro

Let’s review the code, before we run the macro. Make sure to place the cursor in cell address “B2” before we run the macro.

Let’s run the macro. As we can see, the macro is correctly freezing the panes in each worksheet as required.

In summary, here is the example workbook which the video showcased, in order to compose the macro.