Pagebreaks: Grouping rows together so print on same page

jeremy1026

New Member
Joined
Jan 12, 2007
Messages
17
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]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Something like

Code:
Sub pagebreak()

Dim i As Integer

Application.ScreenUpdating = False
For i = 3 To 100 Step 3

Worksheets("Sheet1").Rows(i).pagebreak = xlPageBreakManual

Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
On a similar theme, what about if the groups of rows weren't constantly 3 but ranged between 2 and a variable greater than 2 (no more than around 15 though). Any thoughts?
 
Upvote 0
On a similar theme, what about if the groups of rows weren't constantly 3 but ranged between 2 and a variable greater than 2 (no more than around 15 though). Any thoughts?
jt42cwr

Welcome to the Mr Excel board!

How is the change in your groups identified?

Could we see some sample data? Perhaps use Colo's HTML Maker to post a sample of your sheet? Here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.
 
Upvote 0
The spreadsheet basically forms a delivery schedule. Each delivery route is identified by a route number 1,2,3 etc and each individual drop on the route is identified by the decimal .01, .02 etc.

So as an example for the first three groups:-

Column B
1.01
1.02
1.03
2.01
2.02
3.01
3.02
3.03
3.04
3.05
3.06

Column F would show 1, 2, 3 etc next to the first drop in the route, i.e. 1.01, 2.01, 3.01 etc.

Column B and F are probably the only two columns where the changes in groups of rows can be easily identified as the other columns contain random data.

Sheet is probably too large to use HTML maker
 
Upvote 0
jt42cwr

Try this code. It assumes that column B has a heading in row 1.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InsertPageBreaks()
    <SPAN style="color:#00007F">Dim</SPAN> lr   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    lr = Range("B" & Rows.Count).End(xlUp).Row
    ActiveSheet.ResetAllPageBreaks
    <SPAN style="color:#00007F">For</SPAN> r = 3 <SPAN style="color:#00007F">To</SPAN> lr
        <SPAN style="color:#00007F">If</SPAN> Int(Cells(r, 2).Value) <> Int(Cells(r - 1, 2).Value) <SPAN style="color:#00007F">Then</SPAN>
            Rows(r).PageBreak = xlPageBreakManual
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> r
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Theale OMS Master.XLS
ABCDEFGHIJKLMNOPQRSTU
1323KUEHNE+NAGEL-WAITROSEDIVISION-THEALEDISTRIBUTIONCENTRE
2A4COPIESTO:TRANSPORTFLM/SHUNTER/HELPDESKFAX01908244757A3COPIESDRIVERSRESTROOMWALL/SECURITY/TRANSPORTOFFICE/GOODSINOFFICE
3DISTRIBUTIONMASTERSCHEDULEDELIVERYDAY:Monday
4WAVESTORES68WAVESTORES35TRUNKSSTORES63
5H4=400/1L4=402/3/4M4=405/12M4TL=413/5nilstocksONECASES710TWOCASES457PMCASES5551
6PB=PWITHBARNDOORS0.00%CAGES37CAGES19CAGES291
7DRIVERRTEDRIVERSTARTVEHREGRUNNo.MARSHALLEDANDSTATUSTRAILERSTOREGATEGATEPLNDEP
8INITIALSNO.TIMETRAILERBAYNoREADYONLOADEDTYPENAMESEALTEMPTIME
91.01  1  1RUN102:00
101.02   ALL 757EASTBOURNE 
DMS


Peter,

This is a sample of the spreadsheet. I tried the code but it didn't work correctly. Maybe if you can take a look at this then you may get a better idea of what is required and know what's not working correctly?

I need rows 1 - 8 to print on every page, rows 9 - ?? to fill out the rest of each page. The spreadsheet should only print 1 page wide to cover columns B to AG.

Thanks

Damian
 
Upvote 0
Try this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InsertPageBreaks()
    <SPAN style="color:#00007F">Dim</SPAN> lr   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    lr = Range("B" & Rows.Count).End(xlUp).Row
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$8"
        .PrintArea = "$B$1:$AG$" & lr
        .Zoom = <SPAN style="color:#00007F">False</SPAN>
        .FitToPagesWide = 1
        .Fit<SPAN style="color:#00007F">To</SPAN>PagesTall = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    ActiveSheet.ResetAllPageBreaks
    <SPAN style="color:#00007F">For</SPAN> r = 10 To lr
        <SPAN style="color:#00007F">If</SPAN> Int(Cells(r, 2).Value) <> Int(Cells(r - 1, 2).Value) <SPAN style="color:#00007F">Then</SPAN>
            Rows(r).PageBreak = xlPageBreakManual
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> r
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Peter,

It's coming up with the following error: -

Run time error '13':
Type mismatch

On this line:-

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

any ideas?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top