vba to auto fill day of week and date in left footer of each sheet when you go to print out

[slivesay]

Hello. I am looking for some vba code to add the day of the week to the below wording and to work when printing for all sheets in a workbook.

Currently it says in the footer:

Report Dated: 08/30/2019

Format for wording is: Bahnschrift SemiBold Font, SemiBold Font style and 9 size.

Want it to say:

Report Dated: Friday, 08/30/2019

Or something like that w/ the same format as it is now.

Is this a possibility?

 

[Joe4]

I assume that you are trying to print the current date. Unfortunately, you cannot place formulas in header/footers.
However, we are able to get around this by using VBA, as shown here: https://excelribbon.tips.net/T000559_Using_a_Formula_in_a_Footer.html

The code would look something like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    
    Dim sTemp As String
    Dim ws As Worksheet
    
'   Set wording for date range
    sTemp = Format(Date, "dddd, mm/dd/yyyy")

    For Each ws In Worksheets
        With ws
            .PageSetup.LeftFooter = "&""Bahnschrift SemiBold,SemiBold""&9" & sTemp
        End With
    Next ws
    
End Sub

Note that this code MUST be placed in the "ThisWorkbook" module in order to work automatically when printing.

 

[slivesay]

Thank you so much.

 

[Joe4]

You are welcome.