Freeze panes across selected sheets

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

01:21 - 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.

04:24 - 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. This line of code 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 finalised the freezing of panes across all selected sheets; let’s clear the two object variables: startSheet and startCell for the next time we want to run the macro.

06:05 - We can now re-enable the following Excel properties, which were earlier disabled. 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, and select these three worksheets. Let’s run the macro. As we can see, the macro is correctly freezing the panes in each selected worksheet as required. Don’t forget to send us a comment, like this post or subscribe to our channel. .