FOOTER’S DATE (97)

Topic

New Reply

HOW CAN I INSERT THE TODAY’S DATE ON THE FOOTER AND LET IT STAY UNCHANGED WHEN I PRINT THE DOC ON OTHER DAY.
THANK YOU FOR ANY HELP.

Reply | Quote

Replies

Select Page Setup from the File menu. Click on the Header/Footer Tab in the Dialog Box. Click on the “Custom Footer” button. In the Footer dialog box, click in the section of the footer where you want the date to appear (Left, Center, or Right) and then type in the date you want displayed in the footer. Click OK until all dialog boxes are closed.

You could also use a VBA macro like the one below to set todays date into the footer:

Public Sub SetFooter()
    ActiveWorkbook.Worksheets("Sheet1").PageSetup.CenterFooter = Format(Date, "dd-MMM-YYYY")
End Sub

Reply | Quote

Hey joe,

Did LC’s suggestion work for you? I feel your pain! My only solution was to stop using any of the “auto-date” footer entries and just use plain text that I have to change when I revise the document. All the other things I tried change when I open or print the darn files. Of course, then it is up to me to manually change the date when I revise a doc, but I’m getting used to it.

Hope this helps! – Thomas

Reply | Quote

Try something like this, It gets the LAST saved date. If you do not save your worksheet everyday the date will NOT change.

Steve

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = _
“Last Saved: ” & _
Format(FileDateTime(ThisWorkbook.FullName),”mmmm d, yyyy”)
End Sub

Reply | Quote

The first question is whether you want to do this to every (or many) documents, or just to one? I had a situation where I was printing large financial models reflecting different assumptions, and it was convenient to highlight the assumptions in the footers.

I wrote the following VBA routine to insert specified text in the footers, and inserted a button on the first page of the model to run the routine:

Sub FooterText()
Dim Foot1 As String
Dim Foot2 As String
Dim Foot3 As String
Dim Foot4 As String
Dim Worksht As Object

Foot1 = Range("Footer1").Value
Foot2 = Range("Footer2").Value
Foot3 = Range("Footer3").Value
Foot4 = Range("Footer4").Value
For Each Worksht In Worksheets
    Worksht.Activate
    With ActiveSheet.PageSetup
    .LeftFooter = Foot1 & Chr(13) & Foot2 & Chr(13) & "&F  &A"
    .CenterFooter = Format(Now, "Mmmm d 'yy") & "     " & "&T" _
             & Chr(13) & "page " & "&P" & " of " & "&N"
    .RightFooter = Foot3 & Chr(13) & Foot4
    End With
Next Worksht
End Sub

The named ranges Footer1 to Footer 4 contained formulas to concatenate text as values, for example:

Cell C56 is the named range Footer1:
Cell A56 contains “Maximum Assets eligible for Tax Depreciation: ” (<–note the space after the colon)
Cell B56 contains a formula picking up the calculation from elsewhere on the S/sheet – say $150,256,223
Cell C56 contains "=CONCATENATE(A56, text(B56,"$#,##0.0,,"), " Million")

This produces a nice text string "Maximum Assets eligible for Tax Depreciation: $150.3 Million" – which is then set down in the first line of the left footer. Similar concatenations produce other formated text in the Footer2-Footer4 ranges

As you can see, the center footer contains the date and time that the footers were updated – usually immediately before the model was printed. It is slow (I could speed it up by setting screen updating on and off, but it was really a one-off), but MUCH faster than going and amending the footers in every sheet (about twelve) every time we wanted to try a different set of assumptions (about eight times a day)- which in turn was preferable to not knowing which assumptions lead to the result on each page and constantly having to refer back to the assumptions page….

If you want to be able to do this for a number of s/sheets, include something like:

Sub Datestamp()
ActiveSheet.PageSetup.CenterFooter = Format(Now, "Mmmm d 'yy")
End Sub

In your personal.xls file – that way it will always be available to insert todays date in the centre footer. This will be inserted as a string – it won’t change subsequently.

Reply | Quote

If you would like to show:
1. Date the sheet/book was created and
2. Date the sheet/book was printed
In one footer pane type “Created xx/xx/xxxx” so it is text
In another footer pane insert the date from the footer tools, precede this with the words “revised” or “Printed”

Reply | Quote

Creation date and Printed date
Steve

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = _
“Created: ” & _
format(ThisWorkbook.BuiltinDocumentProperties(“Creation Date”),”mmmm d, yyyy)

ActiveSheet.PageSetup.rightFooter = _
“Printed: ” & _
format (Now, “mmmm d, yyyy”)
End Sub

Another way to enter the saved date:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.centerFooter = _
“Saved: ” & _
format (Now, “mmmm d, yyyy”)
End Sub

Steve

Reply | Quote

Thanks Steve for that information, and for taking the time to help a VBA tyro

Reply | Quote

To get around my problem, in my vba, I put the =today() in b5,copy b5, and pastespecial only value to b5
and use the code below. After this code, clear the b5. In this way, I can permanently save the created date in my footer, no matter I will or not save the file later.
any other idea?

ActiveSheet.PageSetup.Leftfooter = _
Format(Worksheets(“Sheet2”).Range(“B5”).Value)

Reply | Quote