FREEZING TOP ROW IN MULTIPLE WORKSHEETS

[venumkd]

Sirs,

Is it possible to FREEZE TOP ROW of multiple / selected worksheets (500 to 1000 Nos.) in a workbook based on cell values?

The sheet numbers / Names to be top freezed shall be mentioned in the first worksheet in Column A beginning with A1 or wherever you suggest

Thanks & Regards

VENU

 

[My Aswer Is This]

How about a script to freeze top row of all sheets in your workbook. Would that work?
And this would require Vba

 

[venumkd]

No sir
It should not be for all sheets. It will not work out.
It has to be only for selected sheets. Even for upto 100 sheets can be managed.
Thanks & Regards
VENU

 

[My Aswer Is This]

Saying only selected sheets when you have more then one will not work.
Or saying you will enter sheet name or sheet number in column A of sheet 1 could be difficult
A sheet name and a sheet number are different
So if the script sees 23 does that mean the sheet is named 23 or is sheet 23 is not easy for me to deal with.
Now if you said sheets 1 to 200 that could be easy.
But maybe someone else here on the forum will be to help you.

 

[Peter_SSs]

The sheet .. Names to be top freezed shall be mentioned in the first worksheet in Column A beginning with A1 ...

Try this

Sub Freeze_Panes()
    Dim c As Range
    
    Application.ScreenUpdating = False
    For Each c In Sheets(1).Range("A1").CurrentRegion.Resize(, 1)
      Application.Goto Sheets(c.Text).Range("A2")
      ActiveWindow.FreezePanes = True
    Next c
    Application.ScreenUpdating = True
  End Sub

 

[venumkd]

Hi, Sir,
FANTASTIC. It is WORKING EXACTLY as per my requirement.
Thans a lot. Also please let me know what changes are to be made for Unfreeaing those frozen rows?
I may require few more help/
Thanks & Regards
VENU

 

[Peter_SSs]

Hi, Sir,
FANTASTIC. It is WORKING EXACTLY as per my requirement.

Good news. !

what changes are to be made for Unfreeaing those frozen rows?

You could use this ..

Sub UnFreeze_Panes()
  Dim c As Range
 
  Application.ScreenUpdating = False
  For Each c In Sheets(1).Range("A1").CurrentRegion.Resize(, 1)
    Sheets(c.Text).Activate
    ActiveWindow.FreezePanes = False
  Next c
  Application.ScreenUpdating = True
End Sub

.. or perhaps this which will toggle those sheets freeze panes on if they are off and off if they are on. I have also added code so that after the code has executed, you will be left on the sheet you were on when the code was triggered.

Sub Toggle_Freeze_Panes()
  Dim c As Range
  Dim wsAct As Worksheet
  
  Application.ScreenUpdating = False
  Set wsAct = ActiveSheet
  For Each c In Sheets(1).Range("A1").CurrentRegion.Resize(, 1)
    Application.Goto Sheets(c.Text).Range("A2")
    ActiveWindow.FreezePanes = Not ActiveWindow.FreezePanes
  Next c
  wsAct.Activate
  Application.ScreenUpdating = True
End Sub

 

[venumkd]

Hi, Sir,
FANTASTIC. It is WORKING EXACTLY as per my requirement.
Thanks a lot.
I may require few more helps / I will post.
Thanks & Regards
VENU

 

[Peter_SSs]

Hi, Sir,
FANTASTIC. It is WORKING EXACTLY as per my requirement.
Thanks a lot.

You're welcome. Glad it was what you wanted.

I may require few more helps / I will post.

That is fine, though unless the question is a direct follow-on from this one or very closely related, start a new thread for each question.