Printable View
I was wondering if it is possible to do a formula to put the min to max date in the right header from a column. I want it to look in my date column and then use the minimum date and maximum date to look like this (01\01\07-01\10\07). Is this possible?
=TEXT(MIN(A:A),"dd\\mm\\yy")&"-"&TEXT(MAX(A:A),"dd\\mm\\yy")
are you referring to the header & footer??Quote:
Originally Posted by Djblois
Yes - I want to put it in the right header
Modifying Bob's formula, you could add the header by VBA prior to printing
[VBA]Private Sub Workbook_BeforePrint(Cancel As Boolean)
AddHeader
End Sub
Sub AddHeader()
With Application.WorksheetFunction
ActiveSheet.PageSetup.RightHeader = _
.Text(.Min(Columns(1)), "dd\\mm\\yy") & "-" & .Text(.Max(Columns(1)), "dd\\mm\\yy")
End With
End Sub
[/VBA]
I just need help with one more thing the row with dates is on a seperate tab that I have set to detail
[VBA]Sub AddHeader()
Dim ws As Worksheet
Set ws = Sheets("detail")
With Application.WorksheetFunction
ActiveSheet.PageSetup.RightHeader = _
.Text(.Min(ws.Columns(1)), "dd\\mm\\yy") & "-" & .Text(.Max(ws.Columns(1)), "dd\\mm\\yy")
End With
End Sub
[/VBA]
That worked madmackillop but can I add formating to it?
[VBA]ActiveSheet.PageSetup.RightHeader = .text(.Min(Columns(4)), "mm\\dd\\yy") & "-" & .text(.Max(Columns(4)), "mm\\dd\\yy")
ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12"[/VBA]
this doesn't work it just clears out the right header
[VBA]Sub AddHeader()
Dim ws As Worksheet, HeadTxt As String
Set ws = Sheets("detail")
With Application.WorksheetFunction
HeadTxt = .Text(.Min(ws.Columns(1)), "dd\\mm\\yy") & "-" & .Text(.Max(ws.Columns(1)), "dd\\mm\\yy")
End With
ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12 " & HeadTxt
End Sub
[/VBA]
Thank you you are the best