Goal: Permit Excel users to unfreeze panes across selected worksheets in an active workbook.

This article will present a follow up to an earlier video, which demonstrated a macro-based approach to freeze selected worksheets in a workbook. As we highlighted in a prior video, Excel will only enable users to freeze panes in one worksheet at a time.

Similarly, this is the same with unfreezing panes. It will not allow the selection of multiple worksheets to unfreeze panes at the one time.

Hence we need to go to Visual Basis for Applications and write a macro.

The unfreeze panes selected sheets macro

Don’t forget to insert a module. It is recommended to describe the macro, just in case we need to reassess or change the macro at a later date. The macro will be called, unfreezePanesSelectedSheets.

For starters, it is advisable to disable the following Excel properties before the macro runs. Then we need to declare object variables for our unfreeze selected sheets, panes macro. These object variables will be critical in the total and accurate operation of the macro across the selected worksheets. Now we must stipulate the location of some object variables – startSheet and startCell.

We now must add the loop code to the macro. The below 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 unfreeze the panes in the same location for each selected worksheet. The final line of code, Next ws, will return the macro to the next 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 completed the unfreezing of panes across all selected sheets; let’s clear the 2 object variables: startSheet and startCell for the next time we need 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.

Running the macro

Make sure to select the relevant worksheets to unfreeze panes. Let’s run the macro.

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

Download SpreadsheetVault – Unfreeze panes across selected sheets (solution).xlsb