Preserving Column Headers and Key Values

Fields Table with Freeze Column

“Freeze” tells our routines which columns to keep in view no matter where the user scrolls to.  If you don’t want any, I recommend putting a “Y” in your first field.  This will preserve the column headings at minimum.  You can only indicate one field (or none) to freeze.

Add “Freeze” as shown to your fields table and remember to resize the named range “Fields” by placing your cursor on cell “A3″ and pressing Ctrl-Shift-N.

Below is the “Freeze Panes” routine and a minor modification to our Macro1().

Function Freeze_Pane(sDataRange As String, sFieldRange As String) As Boolean

'   Freeze_Pane:        Locks columns and rows from scrolling
'                       (Used to keep column headers and key data displayed)

'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = Range containing data's field definitions
'   Example:            bResult = Freeze_Pane("Data", "Fields_Data")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
   
    Freeze_Pane = Failure          'Assume the Worst
    On Error GoTo ErrHandler

    Settings "Save"     'Save current settings
    Settings "Disable"  'Disable events, screen updates, and calculation
       
    Dim lCol As Long
    Dim lRow As Long
   
    Worksheets(Range(sDataRange).Worksheet.Name).Activate
    ActiveWindow.FreezePanes = False
   
    With Range(sFieldRange)
        lCol = FieldColumn("Freeze", sFieldRange)
        For lRow = 2 To .Rows.Count
            If UCase(Trim(.Cells(lRow, lCol))) = "Y" Then
                Range(sDataRange).Cells(2, lRow).Select
                ActiveWindow.FreezePanes = True
            End If
        Next
    End With

    Settings "Restore"     'Restore previous settings

    Freeze_Pane = Success

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Freeze_Pane - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function 

Modifications to Macro1().
Find the line beginning with If NameExists and replace it with this:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Pivot_Template
    End If
End If