Printing header rows on multiple pages.....

R

Randy Lefferts

I know that I can select multiple, contiguous rows to
print on the top of each page in the worksheet but is
there a way to print 2 sets of rows that are not adjacent?

We have a worksheet that on the first page we need $1:$8
printed and each successive sheet we need $1:$3 and $6:$8
printed. Is there a way to do this? Thanks in advance! :)
 
R

Ron de Bruin

Try this

Sub Test()
Dim TotPages As Long
TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$8"
ActiveSheet.PrintOut From:=1, To:=1
ActiveSheet.Rows("4:5").Hidden = True
ActiveSheet.PrintOut From:=2, To:=TotPages
End With
ActiveSheet.Rows("4:5").Hidden = False
End Sub
 
D

Dave Peterson

Watch out. You may miss a few rows when you print.

When I did this against test data, I printed:

Rows 1:51 on the first sheet.

On the second page, I printed:
1:3, 6:8 and 54:98.

I think excel saw room for more stuff on the sheet and assumed that the first
page should have printed:
1:3, 6:8, and 9:53.
 
R

Ron de Bruin

You are right Dave(as usual)

I have test in with only data in cell a1:a8
I will think about a solution after work
 
R

Ron de Bruin

Hi Randy/Dave

This example you can use if you want to print ? rows
on every page.
This example will print 40 rows of data on every page(A:H in this example)
The example below will print 4 pages.

I am sure Dave have a better suggestion


Sub Test()
Dim Srow As Long
Dim PrintPage As Long
Dim RowsToPrint As Long
Dim HowManyPages As Long

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$8"
End With

Srow = 9 ' start row for printing
RowsToPrint = 40 ' print 40 rows on every page
HowManyPages = 3 ' it don't count the first page

' Print the first page and hide the two rows
Range("A" & Srow & ":H" & Srow + RowsToPrint - 1).PrintOut
ActiveSheet.Rows("4:5").Hidden = True

For PrintPage = 1 To HowManyPages
Srow = Srow + RowsToPrint
Range("A" & Srow & ":H" & Srow + RowsToPrint - 1).PrintOut
Next
ActiveSheet.Rows("4:5").Hidden = False
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


Ron de Bruin said:
You are right Dave(as usual)

I have test in with only data in cell a1:a8
I will think about a solution after work
Click to expand...
 
D

Dave Peterson

No, I don't have a better solution.

I was thinking that formatting rows 4:5 with a custom format of ";;;" to hide
the values, but keep the rows might be acceptible, but that looks pretty ugly.


Hi Randy/Dave

This example you can use if you want to print ? rows
on every page.
This example will print 40 rows of data on every page(A:H in this example)
The example below will print 4 pages.

I am sure Dave have a better suggestion

Sub Test()
Dim Srow As Long
Dim PrintPage As Long
Dim RowsToPrint As Long
Dim HowManyPages As Long

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$8"
End With

Srow = 9 ' start row for printing
RowsToPrint = 40 ' print 40 rows on every page
HowManyPages = 3 ' it don't count the first page

' Print the first page and hide the two rows
Range("A" & Srow & ":H" & Srow + RowsToPrint - 1).PrintOut
ActiveSheet.Rows("4:5").Hidden = True

For PrintPage = 1 To HowManyPages
Srow = Srow + RowsToPrint
Range("A" & Srow & ":H" & Srow + RowsToPrint - 1).PrintOut
Next
ActiveSheet.Rows("4:5").Hidden = False
End Sub
Click to expand...
 
Top