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

This tutorial will present you with a macro-based solution to unfreeze all worksheets in a workbook. This is a follow-up an earlier video, “How to freeze panes across multiple Excel worksheets“. As most Excel users realize, Excel will not allow a user to unfreeze panes in multiple worksheet at the same time.

It will not allow the selection of multiple worksheet to unfreeze panes simultaneously. Therefore we must to go to Visual Basic for Applications and write a macro.

Writing the unfreeze panes worksheets macro

It is recommended to narrate the macro, just in case macro users need to revisit the macro in the future. We will call this macro, unfreezePanesSheets

First, we should disable the Excel properties before the macro starts. Now we want to declare the object variables for the macro.

Note, these object variables will be vital in the proper execution of the macro across all worksheets. Now we must stipulate the location of the object variables – startSheet and startCell

We are now ready to add our loop code.

This code will enable us to move through all the worksheets in the workbook. We will now be able to unfreeze the panes in the same location for each worksheet.

This line will move the macro to the next worksheet

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

The macro has finished the unfreezing of panes across all sheets. We can now re-enable these Excel properties

Using the macro

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

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

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