Custome Header in Excel

[candyman200]

EXCEL - I've got a "low tech" user that needs a header on each page of a report for the month & year ... but I can't expect them to change the header each month. My thought is to have them name the file "January-2004" and I can include that (&DATE) in the header ... but it includes the .xls. Is there a way to strip the file suffix in the header?

 

[zbnet]

You could ditch the application headers, and use some rows of your sheet (which you have more control over the contents of) as the 'header' on each page: the Page Setup - Sheet tab has options to specify rows that repeat at the top of every page.

 

[candyman200]

Thanks zbnet - the report can cover 1, 2 or three pages and I'm never sure how many columns will be on each page. I'm thinking if I center the month/year over 10 columns, repleat on each page it might or might not center correctly on subsiquent pages. I'll try it and see what happens. I hoping that if there's a function (&[DATE] there might be a way to tweak it. Thanks again

 

[jyotko]

Place this bit of code in the Workbook macro section. Whenever the report is printed it will print the current month and day. You could also prompt the user for the information with a more complex macro if you need more flexibility.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

strReportDate = MonthName(Month(Date)) & " " & Year(Date)

With ActiveSheet
.PageSetup.LeftHeader = ""
.PageSetup.CenterHeader = strReportDate
.PageSetup.RightHeader = ""
End With

End Sub