Repeat Sheet headings on each page

[voodoo]

Im trying to find the best way or best alternatives to repeat a sheet heading on all following pages.

This is not actually a header. It is the first 3 or 4 lines (the number could vary) of an actual sheet. This is actually a template with column headings etc for a collection of data.

I want to copy this temaplate to a new workbook to become the output. There is only one sheet but when there is a lot of data requiring many pages (could be 15 - 20 pages) I want this template of column headings copied to every page in the output. So when I press print preview I will see each page with the same identical heading at the top.

The best option I suppose would be to have this functionality built into the template so that I dont have to code anything (just copy and paste it to the new output workbook). But otherwise I would need to know how to code this functionality in VBA in the final output workbook.

I hope that makes sense. Any ideas or opinions would be appreciated.

[Chris Davison]

File
Page Setup
Sheet
Rows to repeat at top = your rows (just select them)

is that what you had in mind ?

[voodoo]

I suppose that is what Im after but the problem is when I set that in the template and then copy the heading from the template and paste to a new sheet in a new workbook this fuctionality is not copied so the new sheet does not repeat these rows.

[Chris Davison]

ahhh, I see, so you want something that'll pick up that and replicate it.... sounds like a VBA solution is needed....

lots of good VBA'ers here, so it shouldn't be too long

[stevebausch]

There is a Workbook_BeforePrint event, in the ThisWorkbook "module", you may want to consider using it to accomplish your goal.

Other thoughts:
Reset the pagebreaks (macro may need to set DisplayPageBreaks as True)


Here is a code snippet that sets the print area to exact pages; each page will have the same number of cells printed, regardless where the last cell entry is.

You may need to ask me some questions as how the code works, that's okay.

Here goes:

SavedView = ActiveWindow.View 'I had trouble when running in Normal view
ActiveWindow.View = xlPageBreakPreview 'So I save the current view, and set to Page Break view
ActiveSheet.PageSetup.PrintArea = "$A$1:$L$160" 'This is the total area of spreadsheet
ActiveSheet.ResetAllPageBreaks
'MsgBox "there are " & ActiveSheet.HPageBreaks.Count & " pagebreaks" 'code left over from debugging

For TheLoop = 1 To ActiveSheet.HPageBreaks.Count 'HorizontalPageBreaks

ThisBreak = ActiveSheet.HPageBreaks(TheLoop).Location.Row 'The row of the pagebreak

' MsgBox "Pagebreak at " & RowOfPageBreak 'code left over from debugging

If RowOfLastText <= ThisBreak - 1 Then 'If row of the pagebreak is past the row of last text, then....
'Set Print Area
ActiveSheet.PageSetup.PrintArea = _
Range(Cells(1, 1), Cells(ThisBreak - 1, 12)).Address
Exit For
End If


Next
ActiveWindow.View = SavedView


OR......Go to Page Setup-Sheet, Rows to repeat at top. Actually, that should be all you need to do.

[stevebausch]

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With

[voodoo]

Thanks Steve that achieves the objective.

I do nkow that another sample macro I have access to does allow the template just to be copied and does maintain the Row repeats when copied an pasted.

It seems to have been acheived by using the Set Print Area but the dotted lines indicating that area spread right down to the bottom of the template sheet and accross. Like the Print Area is alerady set for the possibility of multiple pages. Even though in the template only the heading is shown in the first few rows.

Maybe this is more wwhat I am after o how to set the print area in the template to allow for multiple pages and then just copy and paste it and the heading rows are automtically repeated.