Print Cell Data in heading of pages

[Mike Polinske]

I have an Excel 2003 spreadsheet that I have grouped to page break by a
column when the value changes. I have this column hidden because I don't
want it to print on each row, but in the page header. Is it possible to do
this in Excel? or do I need to export the spreadsheet to Access in order to
get this functionality?

For example, when the officer changes from x to y and new page prints and I
want X to print on page 1 and y to print on page 2 or whatever page the new
officer starts on.

 

[Jim May]

Code by Gord Dibben MS Excel MVP << Found using Google>>

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(2).Rows.Count To 1 Step -1 'change column #
' to your column #
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub

 

[Mike Polinske]

That will insert a page break, but it won't put a cell value in the heading.

I'd like to be able to print the officers name in the heading of the report
instead of on each detail line of the report.

 

[Jim May]

Mike paste your code so that I can take a look at what you now have.
Jim

 

[Mike Polinske]

Currently I do not have any macros in my spreadsheet. I am using the
"Subtotal" from the "Data" menu to page break between groups. So I have at
each change in officer, use function "count" and then I have the checkbox
"Page break between groups" checked. I just can't figure out how to set up
the custom header in "page setup" so that it will include a variable value
from a cell. I saw some macros which would put the same value in every page.
But what I'd like is to take a value from the same column but the first row
on each page.

For example if officer X only has one row; X should be on the header of that
page and then Y (the next officer) should have their name on the header of
their pages. If you want, I can send you a private e-mail with what I have.