VBA Code – To Freeze Pane (Header) Rows 1and 2

[wcm69]

I would like to replicate the freeze pane function using a VBA code. In other words
View > Freeze Panes > Freeze Top Row

I however want to keep Rows 1 and 2 visible whilst scrolling in an active worksheet.

I have a large data workbook with multiple worksheets and only need to freeze pane in specific worksheets and not the entire workbook.

So far the codes I’ve found don’t seem to be working for me .

I’m a VBA noobie so some help solving this issue (including a little instruction, i.e. where to place the code – in the ThisWorkbook, Module or individual Worksheets) would be greatly appreciated.

Thanks in advance .

 

[mas123]

Hello my dear
You can use macro recorder
to get the code for this operation
it will get you this

Sub Macro1()'
' Macro1 Macro
'


'
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 2
    End With
    ActiveWindow.FreezePanes = True
End Sub

 

[wcm69]

Hi mas123,

Thank you for taking the time to reply.

I've placed the code in the individual Worksheet where I want the freeze pane. The code works and does what I need, but I have to run the macro for it to do so .

Can you advise how I can get the code to run automatically, i.e. be activated when the worksheet becomes active.

Your help is greatly appreciated.

Thanks in advance

 

[mas123]

You are welcome
you can do this by putting this code in
thisworkbook
in vba window by pressing ALT+F11

Private Sub Workbook_SheetActivate(ByVal Sh As Object)    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 2
    End With
    ActiveWindow.FreezePanes = True
End Sub

 

[wcm69]

Hi mas123,

Again, Thank you for taking the time to reply and assist me.

The code does what I'm after, but I have 2 worksheets in the workbook (a Contents Page and PQuery Table) which I do not want to have the freeze pane code applied to.

Putting the code in the Workbook has applied the freeze pane to every worksheet in the workbook. Perhaps there's a little tweak that can be made to the code to apply to only 'specified' worksheets. Apologies if I wasn't clear in my Op.

I actually have a total of 53 worksheets in the entire Workbook, so I'll try putting the code into the individual Worksheets to see if that does the trick.

As usual any and all help is appreciated.

Thanks in advance

 

[mas123]

after opening vba window
double click on the sheet that you want
and patse this code

Private Sub Worksheet_Activate()
With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 2
    End With
    ActiveWindow.FreezePanes = True
End Sub

 

[wcm69]

Hi mas123,

Thank you for the code. I've followed your instruction and put the code in the required sheets but when I run the code it has a 'Compile Error' message: 'Ambiguous name detected: Worksheet_Active'.

I'm not sure if there's something I'm not doing correctly .

If you've got any advice on a fix I'd be very grateful. I'm hoping!

Thanks in advance

 

[mas123]

you can do it manually

 

[footoo]

https://www.mrexcel.com/forum/excel...e-%96-unfreeze-panes-specific-worksheets.html
https://www.mrexcel.com/forum/excel...e-%96-unfreeze-panes-specific-worksheets.html