Pagebreaks: Grouping rows together so print on same page

[jeremy1026]

Hi,

I have a report that shows 3 lines for each item. Is there a way to group sets of 3 rows together so they print on the same page so that it would insert a page break either before or after each set of 3 rows, not between them.

Thanks for any help!

[/img]

 

[jt42cwr]

Peter, I have a similar sheet which I need to do the same for, the differences from the first sheet being:-

Column J contains the figures in column B on the first sheet.
The title rows are rows 1 to 12
The print area is from A1 to Z??
There are 46 rows on each sheet excluding the title rows (58 with)

I have changed the code as below where hi-lighted but it didn't work correctly, putting page breaks after the title rows and then every 46 rows from there. Can you let me know what I've doen wrong?

Sub PRINT_PICK_CONTROL()

Dim lr As Long
Dim r As Long
Dim lb As Long
Dim tb As Long
Dim lc As Long
Dim p As Integer
Dim athree As Integer
Dim afour As Integer

Application.ScreenUpdating = False
lr = Columns("J").Find(What:="*", After:=Range("J1"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lb = 13
lc = 13
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$12"
.PrintArea = "$a$1:$z$" & lr
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.ResetAllPageBreaks
For r = 14 To lr
If Int(Val(Cells(r, 2).Value)) <> Int(Val(Cells(r - 1, 2).Value)) Then
If r - lb > 46 Then
Rows(lc).PageBreak = xlPageBreakManual
lb = lc
End If
lc = r
End If
Next r
Application.ScreenUpdating = True
If lr - lb > 46 Then
Rows(lc).PageBreak = xlPageBreakManual
End If

End Sub

 

[Peter_SSs]

Damien

I have a suggestion (not sure if it is the most efficient way) for fixing the print area problem in the first sheet. Try adding the Do...Loop lines of this code where indicated.

<font face=Courier New>    lr = Columns("B").Find(What:="*", After:=Range("B1"), SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Cells(lr, 2).Value = ""
        lr = lr - 1
    <SPAN style="color:#00007F">Loop</SPAN>
    lb = 9</FONT>

For your new problem, try replacing this line

If Int(Val(Cells(r, 2).Value)) <> Int(Val(Cells(r - 1, 2).Value)) Then

with

If Int(Val(Cells(r, 10).Value)) <> Int(Val(Cells(r - 1, 10).Value)) Then

because column B = column 2 whereas column J = column 10. Also, if the values in column J are numbers, you should be able to use

If Int(Cells(r, 10).Value) <> Int(Cells(r - 1, 10).Value) Then

If you look back to my previous code for the first sheet, you will see that the Val() part of the code was removed.

I am going away for a week or so and not sure if I will have much internet access in that time. If your problem is not yet solved keep trying. Either somebody on the board will step in to help or if you try things yourself, you will probably figure it out anyway. Good luck!