Select first row of an Excel table

[Raddle]

Hi

I need to turn on freeze panes from the first row of the table on each sheet.
This should allow the user to scroll but keep the headers.

I can select the table but the .databodyrange argument is not playing ball for me.

Any hints here? I know there has been a lot on this and I have looked but can't quite get there

Sub Acnt_FreezePanes() 'freeze panes for each table
Dim tbl As ListObject
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
ws.Activate ' I have to do this or it does not work at all - no sure why
With ws
For Each tbl In ws.ListObjects
If tbl.name <> "Tbl_FilePath" Then
tbl.Range.Select ' this is where I really want to use databodyrange but I can't fit it in ...
ActiveWindow.FreezePanes = True
Debug.Print ws.name

End If
Next tbl
End With

Next ws
End Sub


Many thanks

 

[JoeMo]

Try:

Sub Acnt_FreezePanes() 'freeze panes for each table
Dim tbl As ListObject
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
ws.Activate ' I have to do this or it does not work at all - no sure why
With ws
    For Each tbl In ws.ListObjects
        If tbl.Name <> "Tbl_FilePath" Then
        tbl.DataBodyRange.Rows(1).Select 
        ActiveWindow.FreezePanes = True
        Debug.Print ws.Name
    End If
    Next tbl
End With
Next ws
End Sub

 

[Raddle]

! yep - that has sorted it.

Pity about databodyrange .. that seemed a nice approach.

Thank you so much!!!

 

[JoeMo]

! yep - that has sorted it.

Pity about databodyrange .. that seemed a nice approach.

Thank you so much!!!

You are welcome - thanks for the reply.