You can use VBA code to create your own headers and footer.  This can give you more flexibility than using the standard header and footer options from the Page Setup dialog box. There are three regions in header and three in the footer: LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, and RightFooter.  All of these properties accept text strings, and are accessible through the Worksheet.PageSetup object:

Worksheets("SomeSheet").PageSetup.LeftHeader = "Some Text"

All of the examples here use LeftHeader.  You can change this to any of the regions listed above.

You cannot place  bitmap images in the header or footer.  Instead, place a picture object somewhere in row one on the worksheet, and set Rows To Repeat At Top from the Page Setup dialog.  This will print below the normal header area.

Cell Values In The Header

You can include the value of a cell in the header. For example, the following code will put the value of cell B5 on Sheet2 into the header of the activesheet.

Full File Name In The Header

The following code will place the full file name, including the path, into the header of the active worksheet.

ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName

Fonts And Font Sizes

You can insert control codes to customize the font, font attributes (bold, italics, etc), and font size. The control code used in the header string is the ampersand &.   The code below will create put the follow formatted text in the header.

Some Bold Courier New Size 10 Text

Worksheets("Sheet2").PageSetup.LeftHeader = _
"Some&B&""Courier New""&10 Bold Courier New Size 10&B&""Arial"" Text"

Note that since the font name must be enclosed in quotes in the header string itself, you must use two double quotes in VBA.

See Formatting Codes For Headers And Footers in the VBA help files for a complete list of control sequences.

Where To Put VBA Code

If you are using Excel97, you can include the VBA code to set up your page headers and footers in the Workbook's BeforePrint event procedure.  For more information about event procedures, click here.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS in Worksheets
    WS.PageSetup.LeftHeader = ThisWorkbook.FullName & " " & _
        Worksheets("Sheet2").Range("A5").Value
Next WS
End Sub

This will update the headers each time a worksheet in the workbook is printed.