Group Editing

[Thomas M.]

Excel 2007

I would like to freeze the top row in all of the worksheets in a workbook.
I remember from earlier versions of Excel that it is possible group all the
sheets together, then edit one sheet and have that change show up on all
sheets. That works if I actually change the contents of a cell, but it does
not seem to work for something like freezing the top row of each worksheet.
What is the best way to freeze the top row of each worksheet?

Thanks for any help that you can offer!

--Tom

 

[Gord Dibben]

Sub freeze_line()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Application.ScreenUpdating = False
ws.Activate
Range("A2").Select 'adjust A2 to suit
ActiveWindow.FreezePanes = True
Next ws
Application.ScreenUpdating = True
End Sub

Assumes no Chart Sheets


Gord Dibben MS Excel MVP

 

[ozgrid.com]

Hi Tom

Use;

Sub FreezePanes()
Dim ws As Worksheet
Dim rCell As Range

Set rCell = ActiveCell
Application.ScreenUpdating = False
For Each ws In Worksheets
Application.Goto ws.Range("A2")
ActiveWindow.FreezePanes = True
Next ws
With Application
.Goto rCell
.ScreenUpdating = True
End With

End Sub

 

[Thomas M.]

Thanks, guys.

I will give both of those options a try.

--Tom