Add Dates to Printed Header

[RonNCmale]

I have this schedule that I'm wishing to add the Pay period it is in to the header. Note it will be a different pay period for each sheet.
Example: January- in the heading it should read pay period: 1/10/16 thru 2/6/16
February- in the heading it should read pay period: 2/7/16 thru 3/5/16
March- in the heading it should read pay period: 3/6/16 thru 4/2/16 etc.
I attached the schedule to give you a more visual.

[rcastilho91]

I know this might not be the most efficient method, but have you considered concatenating?

For that to happen, all you have to do is to insert a formula as:


="1/10/16 thru 2/6/16"

'or

= A1 & " thru " & A2

Unless I missed the point of what you're trying to do?

[RonNCmale]

I know this might not be the most efficient method, but have you considered concatenating?

For that to happen, all you have to do is to insert a formula as:


="1/10/16 thru 2/6/16"

'or

= A1 & " thru " & A2

Unless I missed the point of what you're trying to do?


I added this to the header and could not get it to work. I works in cells but not in the header!!! any other suggestions?

[Aussiebear]

What result are you getting?

[Natasha]

I want to ask which version of Excel you are using?

[RonNCmale]

What result are you getting?

I'm getting
= A1 & " thru " & A2 and not the dates.

[Tom Jones]

Maybe:

= TEXT(A1,"mm/dd/yy") & " thru " & TEXT(A2,"mm/dd/yy")

[SamT]

=CONCATENATE(A1," Thru ",A2)

[Aussiebear]

= TEXT(A1,"mm/dd/yy") & " thru " & TEXT(A2,"mm/dd/yy")

This one from Tom Jones should be on the money

[RonNCmale]

This one from Tom Jones should be on the money

It is on the money, with the exception it doesn't work in the header. It does work in any cell outside of the header or footer. Any way I'm going to just make the changes manually in the header and print the sheets out one at a time with the different pay periods. I hope this makes sense, but thanks to all for the suggestions and your time.

[Tom Jones]

This one from Tom Jones should be on the money


Deh, not all of us, are geniuses like you Aussiebear!

[Aussiebear]

Not tested but you can try this:


Sub Date_Header()
For each sht in Workbook
Activesheet.PageSetup.CenterHeader= Text("A1, dd/mm/yy") & " thru " & Text("A2, dd/mm/yy")
Next Sht
End Sub

[snb]

Private Sub UserForm_Initialize()
ReDim sn(Sheets.Count)

For j = 4 To Sheets.Count
sn(j - 4) = Sheets(j).Name
Next

ListBox1.List = sn
End Sub

Private Sub CommandButton2_Click()
For j = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(j) = True Then
With Sheets(ListBox1.List(j))
.PageSetup.RightHeader = Sheets("Staff").Cells(2, 2).Text
.PrintOut
End With
End If
Next
End Sub

[GTO]

I have this schedule that I'm wishing to add the Pay period it is in to the header. Note it will be a different pay period for each sheet.
Example: January- in the heading it should read pay period: 1/10/16 thru 2/6/16
February- in the heading it should read pay period: 2/7/16 thru 3/5/16
March- in the heading it should read pay period: 3/6/16 thru 4/2/16 etc.
I attached the schedule to give you a more visual.


It is on the money, with the exception it doesn't work in the header. It does work in any cell outside of the header or footer. Any way I'm going to just make the changes manually in the header and print the sheets out one at a time with the different pay periods. I hope this makes sense, but thanks to all for the suggestions and your time.

If I am understanding, when you say "sheet" or "sheets" you are actually referring to the physical printed pages, right? If that is correct and we're wanting to change the header for each pay period, then we'd need to just print each range...

In a junk copy of your workbook, try:

In a Standard Module:


Option Explicit
'
Sub example()
Dim arrAddresses
Dim n As Long
Dim dateStart As Date
Dim dateEnd As Date
'
dateStart = DateSerial(2016, 1, 10)
dateEnd = dateStart + 27
'
arrAddresses = Array("$A$1:$AI$49", "$A$50:$AI$99", "$A$100:$AI$149", "$A$150:$AI$199", _
"$A$200:$AI$249", "$A$250:$AI$299", "$A$300:$AI$349", "$A$350:$AI$399", _
"$A$400:$AI$449", "$A$450:$AI$499", "$A$500:$AI$549", "$A$550:$AI$599", "$A$600:$AI$649")
'
For n = 0 To UBound(arrAddresses)
With Sheet2
.Activate
With .PageSetup
Application.PrintCommunication = False
.PrintArea = arrAddresses(n)
.RightHeader = "Prepared By:&U" & Chr(10) & _
"Pay Period: " & Format(dateStart, "m/d/yy") & " thru " & Format(dateEnd, "m/d/yy")

Application.PrintCommunication = True
End With
.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
DoEvents
dateStart = dateStart + 28
dateEnd = dateStart + 27
End With
Next
'
End Sub


Hope that helps,

Mark

[RonNCmale]

GTO, That is exactly what I wanted to do. Thanks GTO and everyone that posted and helped.

[GTO]

Glad we were able to help and you are of course most welcome :friends: