BeforePrint Event

[jfuoti]

I have the following code in the BeforePrint section of VBE. The function works correctly when I print from the worksheet, but does not update the header when I print from a macro - even though it is accessing the event (I checked with debugger) Anyone have any ideas to correct this? Thanks!

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Title
Title = ActiveSheet.Name & " " & Format(Right(ActiveWorkbook.Names("DataDate").Value, 5), "mm/dd/yyyy")
With ActiveSheet.PageSetup
.LeftFooter = ActiveWorkbook.FullName
.CenterHeader = Title
End With
End Sub

[NateO]

You could split this macro out to call the procedure from a normal module. Something along the lines as follows:

In the this workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.Run ("setUp")
End Sub

And in normal modules:

Private Sub setUp()
Dim Title
Title = ActiveSheet.Name & " " & Format(Right(ActiveWorkbook.Names("DataDate").Value, 5), "mm/dd/yyyy")
With ActiveSheet.PageSetup
.LeftFooter = ActiveWorkbook.FullName
.CenterHeader = Title
End With
End Sub

Sub Othermacro()
Application.Run ("setUp")
ActiveSheet.PrintOut
End Sub

Hopefully this helps.

[jfuoti]

Thanks Nate, I went with code in a normal module to put the header & footer on all sheets before I print any:

For Each Worksheet In Worksheets
Dim Title
Title = "&B &14" & Worksheet.Name & " " & Format(Right(ActiveWorkbook.Names("DataDate").Value, 5), "mm/dd/yyyy")
With Worksheet.PageSetup
.LeftFooter = ActiveWorkbook.FullName
.CenterHeader = Title
End With
Next Worksheet