If you want to programmatically set the "Rows to Repeat At Top" option on the Sheet tab in Excel's Page Setup dialog box, here are two ways to do it.
If you already know what range you want, you could use this code:
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
This would set row 1 (which would ideally be a list of column headers) to print at the top of every page.
To set this option based on a user selection:
ActiveSheet.PageSetup.PrintTitleRows = Selection.Areas(1).Address
This code assumes that you have selected at least one entire row, i.e. select rows 1 through 4 then use the code above to set "Rows to repeat at top" to $1:$4.
HTH,
JP
About JP
3 Comments:
Excellent tip!! I've always puzzled over how to do this.
Thanks for another awesome post. I love Excel
I have multiple page documents where I use the repeat function for selected rows.. I need this function on all pages to print on the top except the last one.
Is there a way to define what pages for the selected rows to print on?
I don't think you'll be able to do what you want with the code from this post. You'll need to set the property, print each page (one at a time, using code from http://www.ozgrid.com/VBA/printed-pages.htm to determine the number of printed pages), then turn off the property on the last page.