how do i freeze panes so they appear in all the worksheet tabs

[Dave Peterson]

It's one of those things that works on the window. So you have to select each
of the sheets and set the freeze panes individually.

daveg wrote:

 

[Roger Govier]

Hi

Freeze Panes is a Worksheet attribute, not a Workbook attribute, so you
would need to set it up for each sheet individually.
You cannot Group sheets and apply it, it has to be carried out sheet by
sheet.

You could use the following macro if you have a lot of sheets in your
Workbook, and this will loop through all sheets setting the freeze pane
for the Top row and Left column by selecting cell B2. Change this value
if you want freeze panes in a different position.

Sub FreezePanes()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("B2").Select
ActiveWindow.FreezePanes = True
Next
End Sub

You can copy the code and paste it into your Visual Basic Editor (VBE)
in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

To run the macro, Tools>Macros>Macro>click on FreezePanes>Run

For more help on inserting code into workbooks, David McRitchie has lots
of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[roy may]

how about a feature for selecting a range of sheets in a workbook. (don't want all sheets, just a subset)

 

[Gord Dibben]

CTRL + Click on each sheet you want selected if non-contiguous.

SHIFT + Click to select contiguous group of sheets.


Gord Dibben MS Excel MVP