How do I get the day of the week on the header with the date

[brich2003]

I have a report that someone sends to my printer everyday, it has the date and time on it but I would like to have the day of the week on the report as well.

 

[kskinne]

Re: How do I get the day of the week on the header with the

hi and welcome to the board - the following code will add the day of the week to the header in your workbook

Sub DayInHeader()
    With ActiveSheet.PageSetup
        .LeftHeader = "&T &D"
        .RightHeader = "Day No. " & Weekday(Date)
    End With
End Sub

the code needs to be placed in a new module. modify as needed - post back if you need any other help


hth
kevin

 

[Iridium]

Re: How do I get the day of the week on the header with the

This doesn't work as my VBA knowledge isn't up up scratch but I think something on this line may work so jump in you VBA stars!

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myrange
Set myrange = Worksheets("Sheet1").Range("C14")
Worksheets("Sheet1").PageSetup.LeftHeader = myrange.Value
End Sub

C10=TODAY()
C14=TEXT(WEEKDAY(C10),"dddd")

????????????

Hopefully there will be some useful contributions!

 

[kskinne]

Re: How do I get the day of the week on the header with the

there's no need to place a volative formula into C10 or C14, since this can all be declared in the macro (see my previous post)

kevin

 

[Iridium]

Re: How do I get the day of the week on the header with the

there's no need to place a volative formula into C10 or C14, since this can all be declared in the macro (see my previous post)

kevin

I'd hoped someone more capable than I would jump in - I knew there must be an easier way - as I said my VBA isn't up to speed!

 

[Marc]

Re: How do I get the day of the week on the header with the

If you want the "name" of the day of the week in your header try this. Marc

PS. Paste this code in the "WorkBook" object

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim MyWeek, MyDay2
MyWeek = Array("Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
MyDay2 = MyWeek(WeekDay(Date))
ActiveSheet.PageSetup.RightHeader = MyDay2
End Sub

 

[kskinne]

Re: How do I get the day of the week on the header with the

or, you could just do this:

.RightHeader = Format(Weekday(Date), "dddd")


kevin

 

[Marc]

Re: How do I get the day of the week on the header with the

or, you could just do this:

.RightHeader = Format(Weekday(Date), "dddd")


kevin

Sure you could, but wouldn't that be easier. You’d miss out on having all that fun with arrays.

My brain must be wired to tackle these things in the most complicated way.

I dig seeing simple and effective code. Good Call kskinne!!

 

[brich2003]

Re: How do I get the day of the week on the header with the

Hate to sound like a dummie but I havent used code before and I'm not very familiar with macros. How do I go about adding this code to my spreadsheet?

 

[kskinne]

Re: How do I get the day of the week on the header with the

sorry about that - here's what you need to do:

go to the tools menu, select macro, visual basic editor. this will open the VBE window, with two window panes - it will show any 'projects' (or files) you have open on the left side in a narrow window pane, and on the right is the area where you would add any code for specific modules within your project. what you need to do is double click on the 'this workbook' module in the left-hand pane. This will open the code window on the right. then paste the following code in the code pane:

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
For each sheet in ActiveWorkbook.Sheets
    With sheet.PageSetup 
        .LeftHeader = "&T &D" 
        .RightHeader = "Day No. " & Format(Weekday(Date),"dddd")
    End With 
Next
End Sub

this code will run before each print and will place the same header in each sheet of your workbook. this should get you started. post back if you have any other questions.

hth
kevin