Goal: Enable Excel users to freeze panes in the same position, across all worksheets in all active workbooks.
This article will present you a macro-based solution to freeze all worksheets, across all open workbooks. This is a follow-up to an earlier video, on freezing panes all worksheets in a workbook.
As most people who use Excel realize, users are only able to freeze panes in one worksheet, in one workbooks instantaneously.
Remember, Excel will not permit the selection of multiple worksheet over workbooks to freeze panes simultaneously. Hence, we need to go to Visual Basis for Applications and compose a macro.
Writing the freeze panes worksheets workbooks macro
It is preferable to notate the macro, just in case macro users have to review or edit the macro at a later date. We will call this macro, freezePanesSheetsWorkbooks
For starters, it is best to disable the Excel properties before running the macros. Now we want to declare object variables for the macro.
These object variables will be vital in the correct operation of the macro across all worksheets and workbooks. Now we shall stipulate the location of some object variables – startWorkbook, startSheet and startCell
We are now ready to compose the first loop code.
This code will allow us to move across all the open workbooks. The second loop will allow the macro to shift across worksheets in an individual workbook
We will then be able to freeze the panes in the same location for each worksheet in a respective workbook. The code, “Next ws” will move the cursor to the next worksheet in the underlying workbook.
Once the macro has moved through all the worksheets across all the workbooks, we need to return the cursor to the initial worksheet in the first workbook and cell address “A1”.
The macro has finalized the freezing of panes across all sheets. We are now ready to re-enable the following Excel properties.
Road-testing 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 witness, the macro is correctly freezing the panes in each worksheet across each open workbook as required.
In summary, here are the example workbooks which the video showcased, in order to compose the macro.
