Using Window Panes to Define Work Range

I often find myself in the following situation. I have a database sheet or output page for a macro that has header rows, columns or both. My macro needs to send output to this database-type sheet. Somehow in my code I need to define the output range as the area of the worksheet that does not include the header rows or columns.

Instead of defining a workbook named range, I started playing around with the idea of defining the work area by referencing the non-frozen part of the active window frame. The following simple macro pastes a list of all defined workbook names to the active worksheet. If you use Freeze Panes to allow the header rows and columns to stay visible, the data is always output to the non-frozen pane.

Private Sub Paste_Name_List()

    Dim n As Integer
    Dim WorkArea As Range

'   Define the work area range
    n = ActiveWindow.Panes.Count
    Set WorkArea = ActiveWindow.Panes(n).VisibleRange

'   Clear the work area
    WorkArea.ClearContents

'   Paste list of defined workbook names
    WorkArea.ListNames

End Sub

XL-Logic.com

Using Window Panes to Define Work Range