Hello this video 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
across all worksheets in a workbook.
As most people who use Excel realize,
Excel will only allow users to freeze panes in
one worksheet, in
one workbook instantaneously.
It will not permit the selection of multiple
worksheets across workbooks
to freeze panes simultaneously.
Therefore we need to go to Visual Basis for
Applications and compose a macro.
Next insert a module.
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 macro.
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.
This 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 the respective workbook.
This code will move the cursor to the next
worksheet in the underlying workbook.
Once the macro has moved through all
the worksheets in a specific, open workbook,
we need to return the cursor to
the initial worksheet in this workbook
and cell address "A1".
The macro has finalized the
freezing of panes across all sheets.
This code will move the cursor
to the next open workbook.
Whilst this line of code will move the
macro back to the original open workbook.
We are now ready to 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 witness, the macro is correctly
freezing the panes in each worksheet
across each open workbook as required.
Feel free to comment to this video, like
this video or subscribe to our channel.