Excel Footer with Formatted Date

It’s Fancy Footer Friday! Check with your boss – maybe you can leave early to celebrate.

This week, I’ve been working on Excel printed reports, and one of my clients wanted some fancy features in the footer. There are built-in footer options in Excel, but my client wanted to pull information from the worksheet, and format the date, so we needed some footer programming.

Add Order Information to Footer

In this example, I’ve created an order sheet, and named the cells where the Order Date, Customer Name, and Order Number are entered.

Run the Excel Footer Macro

Then, the SetFooter code, shown below, is pasted into a module in the workbook. When you run the SetFooter macro, it adds the customer name and order number in the left footer.

The line break is created with the Chr(10) code.

In the right footer, the order date appears, formatted as dd-mmm-yyyy.

Excel Footer Sample Code

If you download the sample workbook, you’ll see the code that is used to add the Order Date, Customer Name, and Order Number to the footer.

Modify the Code

Copy the code to your workbook, then modify the code, so it has the sheet name and range names used in your workbook.

You’ll have to change the following lines of code.

Set wsO = wb.Sheets("Orders")
dtmDate = .Range("OrderDate").Value
strCust = .Range("CustName").Value
strOrder = .Range("OrderNum").Value

You can also change the date format in the code, if you prefer something different. For example, change the date format to the following.

Format(dtmDate, "yyyy-mm-dd")

You could change the position of the items too — put the date in the Left Footer, and the other information in the Right Footer.

Download the Sample File

To get the VBA code, and test the custom footer macro, you can download the Excel Footer Macro sample workbook.

The file is in Excel 2007/2010 format, and zipped. When you unzip the file and open it, enable macros, so you can run the code.

Watch the Excel Footer Macro Video

To see the steps for setting up the named cells, and an explanation of how the code works, you can watch this short Excel video tutorial.

_____________________