Automate Page Header Text

Is there a code I can apply in the page header that will read the mininum and
maximum date in a range of cells?

For example:
I would like the page header for some worksheets within a workbook to read
"Data Report from 'minimum date here' thru 'maximum date here'.

So, let's say that I had a pivot table that is refreshing the summarized
data from worksheet1 that has dates in some cells. I would like to refer
back to that so I can automate a date range in the page header for my pivot
table. Is this possible?

You could use a macro:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim MaxDate As Date
Dim MinDate As Date

With Me.Worksheets("sheet1")
MinDate = Application.Min(.Range("a1:X1"))
MaxDate = Application.Max(.Range("a1:x1"))

.PageSetup.CenterHeader = "Data Report from " & _
Format(MinDate, "mm/dd/yyyy") & _
" through " & _
Format(MaxDate, "mm/dd/yyyy")

End With
End Sub

Change the sheet name and range address (both spots) to what you need.

This goes behind the ThisWorkbook module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




Vicki wrote:
>
> Is there a code I can apply in the page header that will read the mininum and
> maximum date in a range of cells?
>
> For example:
> I would like the page header for some worksheets within a workbook to read
> "Data Report from 'minimum date here' thru 'maximum date here'.
>
> So, let's say that I had a pivot table that is refreshing the summarized
> data from worksheet1 that has dates in some cells. I would like to refer
> back to that so I can automate a date range in the page header for my pivot
> table. Is this possible?

--

Dave Peterson