Headers and Footers

A common need in Excel is to print information in the page header or footer that either comes from the worksheet cells or is not available in the standard header and footer options. You might want to insert a company name that is part of the data in the worksheet and display the full path to the workbook file.

The filename is available as an option in headers and footers in Excel 2007. It can be inserted using the code &F, as shown in the following code. Data can be accessed from worksheet cells in the usual way. You can insert this information using the BeforePrint event procedure to ensure it is always up-to-date in reports. The following procedure puts the text in cell A1 of the worksheet named Profit in the left footer, clears the center footer, and puts the filename in the right footer. It applies the changes to every worksheet in the file:

Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wks As Worksheet Dim sFileName As String Dim sCompanyName As String

'Set up values & codes for footer sCompanyName = Worksheets("Profit").Range("A1").Value sFileName = "&F" 'Code generating file name

For Each wks In ThisWorkbook.Worksheets

'Define footer With wks.PageSetup

.LeftFooter = sCompanyName .CenterFooter = "" .RightFooter = sFileName End With

Next wks

End Sub

The footer can be seen in Page Layout View, as shown in Figure 9-3.

Figure 9-3
0 0

Post a comment