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

This video will show you a macro-based solution to freeze selected worksheets in a workbook. As we presented in an earlier video, Excel will only allow a user to freeze panes in one worksheet at a time.

It will not permit the selection of multiple worksheets to freeze panes simultaneously. Thus we need to go to Visual Basis for Applications and compose a macro.

The freeze panes selected worksheets macro

Remember to insert a module. It is prudent to notate the macro, just in case we have to review or edit the macro in the future. We will call this macro, freezePanesSelectedSheets

Firstly we should disable these Excel properties before the macro commences. Next we want to declare object variables for our freeze selected sheets, panes macro. These object variables will be vital in the complete and correct operation of the macro across the selected worksheets

Now we must stipulate the location of some object variables – startSheet and startCell.

We are now ready to compose our loop code. This code will permit us to shift through all the selected worksheets in the workbook; courtesy of the ActiveWindow.SelectedSheets syntax. Hence, we will then be able to freeze the panes in the same location for each selected worksheet.

Once the macro has shifted through all the selected worksheets, we want to return the cursor to the initial worksheet and cell address A1. The macro has finalised the freezing of panes across all selected sheets; let’s clear the 2 object variables: startSheet and startCell for the next time we want to run the macro.

We can now re-enable the following Excel properties, which were earlier disabled. Let’s review the code, before we run the macro.

Using the macro

Make sure to place the cursor in cell address B2 before we run the macro, and select these 3 worksheets. Let’s run the macro.

As we can see, the macro is correctly freezing the panes in each selected worksheet as required.