Macro to freeze panes to all worksheets in my workbook.

[Johnny Thunder]

Hello all,

I am trying to include code into the end of my macro that will look in all worksheets and freeze pane row $1:$1

Any ideas?

 

[wigi]

For instance:

Sub Macro1()
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        With ActiveWindow
            .SplitColumn = 0: .SplitRow = 1
            .FreezePanes = True
        End With
    Next
End Sub

 

[Johnny Thunder]

The .split column and row is the positioning?

 

[wigi]

Yes, since you asked for row 1.

 

[Johnny Thunder]

So if I needed row 3 I would only update the "1" to "3"?

 

[wigi]

It won't hurt to test it out in a blank workbook...







Yes, you're right.

 

[Johnny Thunder]

LOL! Thanks!

You wouldn't happen to know how to execute something similiar that would be applied to all worksheets. I need to when printing a tab have row $1:$1 repeated?

When I use my macro to clear the worksheet contents it always removes print settings like that row repeat.

 

[Johnny Thunder]

Hey Wigi,


I am trying to remove all the freeze panes using the same macro and changing

Freezepanes = True to Freezepanes = False but not luck?


Any ideas on how to remove? I have a macro that deletes all contentbut it won't remove the freeze panes?

 

[wigi]

ActiveWindow.FreezePanes = False

should work, inside the loop over the sheets.

 

[Unite]

I have Excel 2007 and would like to know how to set up a macro that will unfreeze the current panes and freeze another pane for all worksheets in the file I am currently working in. Actually in the current setting, a certain line has been split (View - Split). So need to undo that and freeze panes on another line. I have developed the following code:

Sub Macro1()
Range("E9").Select</SPAN>
With ActiveWindow</SPAN>
.SplitColumn = 0</SPAN>
.SplitRow = 0</SPAN>
End With</SPAN>
Application.ScreenUpdating = False</SPAN>
For Each ws In ThisWorkbook.Worksheets</SPAN>
ws.Activate</SPAN>
With ActiveWindow</SPAN>
.SplitColumn = 4: .SplitRow = 7</SPAN>
.FreezePanes = True</SPAN>
End With</SPAN>
Next
</SPAN>End Sub

When I run this macro, it goes to my "personal" workbook and runs it. How do I save a macro and run it on my the file I am currently in? New to macros, so kindly be detailed.