Hello this presentation will
walk you through you a macro-based solution
to freeze all worksheets in a workbook.
As most Excel users know, Excel will only
permit a user to freeze panes in
one worksheet at a time.
It will not permit the selection of multiple
worksheets to freeze panes simultaneously.
Therefore we need to go to Visual Basic
for Applications and compose a macro.
Remember to insert a module.
It is advisable to notate the macro,
just in case we need to review
or edit the macro at a later date.
We will call this macro, freezePanesSheets
Firstly, we need to disable the Excel
properties before the macro runs.
Now we need to declare object
variables for the macro.
These object variables will be crucial in the
proper operation of the macro
across all worksheets.
Then we need to stipulate the location of the
following object variables -
startSheet and startCell.
We are now ready to compose our loop code.
This code will allow us to cycle through
all the worksheets in the workbook.
We will then be able to freeze the panes in
the same location for each worksheet.
This line of code will move the
macro to the next worksheet.
Once the macro has moved through all the
worksheets, we want to return the cursor to
the initial worksheet and cell address "A1".
The macro has finalized the freezing
panes across all sheets.
We can now re-enable the
following Excel properties.
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 see, the macro is correctly freezing
the panes in each worksheet as required.