VBA Freeze Panes on Multiple Very Hidden Sheets

[nitrammada]

Hi Everyone
I have some code that will FreezePanes at cell B7 perfectly, but I'm trying to run this on all of the veryhidden sheets where cell A1 = L2, without me having to open each one and then run the code.
Can any one tell me what I'm doing wrong please? My code is below.

Sub FreezeL6()
'
' Apply Freeze at cell B7 to all sheets

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Range("A1") = "L2" Then
            ActiveWindow.SmallScroll Down:=-1000
            Range("B7:B7").Select
            With Selection
                ActiveWindow.FreezePanes = True
            End With
        End If
    Next ws

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Any direction or assistance is always appreciated.
Many thanks in advance.
Adam

 

[AlphaFrog]

Try this,

Sub FreezeL6()
    '
    ' Apply Freeze at cell B7 to all sheets
    
    Dim ws As Worksheet
    Dim lWS_Visible_State As Long
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        
    For Each ws In Worksheets
        If ws.Range("A1") = "L2" Then
            lWS_Visible_State = ws.Visible
            ws.Visible = xlSheetVisible
            Application.Goto ws.Range("B7")
            ActiveWindow.FreezePanes = True
            ws.Visible = lWS_Visible_State
        End If
    Next ws
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[nitrammada]

Try this,

Sub FreezeL6()
    '
    ' Apply Freeze at cell B7 to all sheets
   
    Dim ws As Worksheet
    Dim lWS_Visible_State As Long
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
       
    For Each ws In Worksheets
        If ws.Range("A1") = "L2" Then
            lWS_Visible_State = ws.Visible
            ws.Visible = xlSheetVisible
            Application.Goto ws.Range("B7")
            ActiveWindow.FreezePanes = True
            ws.Visible = lWS_Visible_State
        End If
    Next ws
   
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Wow! How do you do that? Thank you AlphaFrog, it worked perfectly, I as so grateful, I would never have come up with that in a million years. I am now going to study your code to understand this as it will help me other routines I have to perform of a similar nature, you have taught me so much, plus saved me hours, thank you so much.
Regards
Adam

 

[AlphaFrog]

You're welcome. Thanks for the feedback.