excel macro for footer - HELP please

[aosaben]

I currently have a macro that formats the header and footer. I would like the date in the left footer to automatically update to the current date either when I open the workbook or before printing.

I am a bit of a novice when it comes to visual basic.

Here is the code I currently have.

Sub MDD_New()
'
' MDD_new Macro
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
End With


Range("A1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveSheet.PageSetup.CenterFooterPicture.Filename = _
"C:\footer\MDD_Footer.jpg"

Private Sub Workbook_Open()
Range("A33").Value = Date

With ActiveSheet.PageSetup
.LeftFooter = "&""Arial,Regular""&8&F" & Chr(10) & Format(Now, "dd-mmm-yy")
.CenterFooter = "&G"
.RightFooter = "&""Arial,Regular""&8 "
.RightHeader = "&""Arial,Bold""&10&USchedule &A&""Arial,Regular""&U" & Chr(10) & "&8Page &P of &N"
.Orientation = xlLandscape
.FooterMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(1)
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.ScaleWithDocHeaderFooter = False
.AlignMarginsHeaderFooter = True
.PrintTitleRows = "$1:$8"
End With
End Sub

Thanks for your help.

 

[wigi]

I currently have a macro that formats the header and footer. I would like the date in the left footer to automatically update to the current date either when I open the workbook or before printing.

You're lucky, since Excel VBA knows both a Workbook_Open event and a Workbook_BeforePrint event. Using these events, you can specify code to be executed at workbook opening or before printing. Put your code to change the footer in either of these events.

 

[aosaben]

Thanks for the info.

I ran into a slight problem in using the workbook_beforeprint.

I added code to format the left footer before printing, however, there are 2 worksheets i need to exclude: "Cover" and "Index"

I have tried to research the if codes, but I do not have enough language knowledge. How would i go about this?

 

[Dartagnan]

Try the following. Put this code in ThisWorkbook.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet2" Then
{Put Your code here}
End If
Next ws
End Sub

 

[aosaben]

Thank you. However, what would the code be to exclude certain sheets (for example a sheet named "Cover"). The names of sheets I want to update will be variable. The ones I want excluded will be static.

Thanks again

 

[Dartagnan]

This should work

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'Sheets want excluded go here using the Or. Keep adding as needed.
If ws.Name = ("Sheet2") Or ws.Name = ("Applicant") Then
Else
{Your Code Here}
Next ws
End Sub