Header that auto displays the previous month

[Stuck1]

Hi all,

I want to display the text: "Period Report to: December 2009" on the print out of a report I produce every month. I always publish the report at the start of the following month, so December's report will come out in January and January's in Fenruary, etc.

I know I could change the headers every month, but I always forget to do it. I have this code, but don;t know how to get it to say the previous month instead of today's date:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.RightHeader = "& Period Report to: " & Format(Now, "DD-MMM-YY")
End With
End Sub

Any help would be great.

Thanks,

Lee

 

[delaneyjm]

Try this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.RightHeader = "& Period Report to: " & Format(DateAdd("m", -1, Date), "mmmm")
End With
End Sub

 

[Stuck1]

Thank you. That works great.

 

[snb_]

or

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.RightHeader = "& Period Report to: " & Format(Date-28, "mmmm yyyy")
End Sub

 

[Stuck1]

Thanks for that too.