Print Setup with varying header rows

[skalaima]

Hello,

I have a spreadsheet which has numerous "header rows" and corresponding data for each header. I should clarify that I'm not talking about "view - header and footer". There are various rows in the spreadsheet which I'm referring to as the "header".
When the file prints I would like to ensure a header is at the top of each page and there are 30+ pages so manually adjusting the page breaks isn't feasible. I'm familiar with using "Rows to repeat at top" under page setup, but that won't do the trick because there are different headers throughout the sheet.

Any ideas?

 

[VoG]

So how do we know which rows are headers?

 

[skalaima]

Thanks for the feedback.
Fortunately the "header rows" do have something to uniquely ID them by. All header rows have "Spec Page" in column E.

 

[VoG]

Maybe this in the ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWorkbook.SelectedSheets
    LR = .Columns("E").Find(What:="Spec Page", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    .PrintTitleRows = "$1:$" & LR
Next ws
End Sub

 

[skalaima]

I get the following compile error: "Invalid or unqualified reference"
The errors occurs in the formula for "LR".

I've never written a macro under "ThisWorkbook" before, but I'm assuming to run it I go to Debug > Compile VBAProject?
I've always written macros under modulels and to run them I just assign them to a button on the sheet or press the play button in the visual basic editor so I'm a little confused with utilizing a macro under "ThisWorkbook".

 

[VoG]

Press ALT + F11, in the Project window double click ThisWorkbook then paste the code in the white space on the right.

 

[skalaima]

I appreciate the quick response and I've done what you suggested. However, when I go to print I get the same error though.
Compile error:
Invalid or unqualified reference

 

[VoG]

Oops!

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWorkbook.SelectedSheets
    With ws
        LR = .Columns("E").Find(What:="Spec Page", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        .PrintTitleRows = "$1:$" & LR
    End With
Next ws
End Sub

 

[skalaima]

I now get a new compile error. It says "Method or data member not found and the ".PrintTitleRows = ..." is highlighted.

Is the ".PrintTitleRows" part of the formula for the variable "LR"? When I copy your code over it shows up on a separate line so I take it the answer is no?

 

[VoG]

Try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWorkbook.SelectedSheets
    With ws
        LR = .Columns("E").Find(What:="Spec Page", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        .PageSetup.PrintTitleRows = "$1:$" & LR
    End With
Next ws
End Sub