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]

 

[Jubjab]

Something like

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

 

[jeremy1026]

Thanks, Jubjab! Exactly what I needed!

 

[jt42cwr]

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?

 

[Peter_SSs]

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.

 

[jt42cwr]

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

 

[Peter_SSs]

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>

 

[jt42cwr]

Theale OMS Master.XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	323	KUEHNE+NAGEL-WAITROSEDIVISION-THEALEDISTRIBUTIONCENTRE
2		A4COPIESTO:TRANSPORTFLM/SHUNTER/HELPDESKFAX01908244757A3COPIESDRIVERSRESTROOMWALL/SECURITY/TRANSPORTOFFICE/GOODSINOFFICE
3		DISTRIBUTIONMASTERSCHEDULE																DELIVERYDAY:	Monday
4							WAVE		STORES			68	WAVE		STORES	35	TRUNKS		STORES		63
5		H4=400/1L4=402/3/4M4=405/12M4TL=413/5				nilstocks	ONE		CASES			710	TWO		CASES	457	PM		CASES		5551
6		PB=PWITHBARNDOORS				0.00%			CAGES			37			CAGES	19			CAGES		291
7	DRIVER	RTE	DRIVER	START	VEHREG	RUNNo.	MARSHALLEDANDSTATUS								TRAILER			STORE	GATE	GATE	PLNDEP
8	INITIALS	NO.		TIME		TRAILER	BAYNo		READY		ON		LOADED		TYPE			NAME	SEAL	TEMP	TIME
9		1.01				1											1	RUN1			02:00
10		1.02													ALL		757	EASTBOURNE
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

 

[Peter_SSs]

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>

 

[jt42cwr]

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?