Printing header on all but last page

[Guest]

I have a specific row I want to print as a header for all but the last page.
I have tried using :

Totpages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
..PrintTitleRows = "$20:$20"
ActiveSheet.PrintOut From:=1, To:=Totpages-1
..PrintTitleRows = ""
ActiveSheet.PrintOut From:=Totpages, To:=Totpages

But the problem is that it will do two passes in preview. I need to have
the entire sheet formated properly in one pass so I can convert to a PDF
document.

The other problem is that the sheet can vary in length from 2 to 10 pages so
I cannot hardwire anything.

I really need a routine that can test what rows are in each page, or
possibly search for a unique keyword within the page. If found, insert
header (.PrintTitleRows = "$20:$20") otherwise do not do insertion
(.PrintTitleRows = "").

Thanks in advance

 

[Dave D-C]

It seems to me that you will need to do that programatically
by inserting or copying your header row(s). You might want
to copy your sheet to another sheet and do this:
Sub sub1()
Dim iHBrk%, iRow&
iHBrk = 1
iRow = 1
Do
' maybe somebody else can explain why this next statement
' is needed (at least in excel 97)
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Select
If iHBrk > ActiveSheet.HPageBreaks.Count Then Exit Do
Rows(iRow).Insert
Cells(iRow, 1) = "Header"
iRow = ActiveSheet.HPageBreaks(iHBrk).Location
iHBrk = iHBrk + 1
Loop
End Sub

 

[Guest]

Thanks Dave,

Only thing I found was that I needed to modify one line of your code as I
got a type mismatch error:
iRow = ActiveSheet.HPageBreaks(iHBrk).Location.Row
I am running Acces 2000

Since I did not want the file to have those inserts in my saved file I just
created an array of row numbers inserted and after preview/print they are
removed. Seems to work fine, awkward but does the trick.
You may be right in doing a copy to another sheet and print/preview that one.
Then I can re-use that sheet as workspace and the original sheet is untouched.
My next little project.

Don