Page break for condition

[i8ur4re]

Good day, I have an excel sheet that ha various prices. I would like to add a page break for each page. The way i have it setup is as so.

ITEM	DESCRIPTION	PAGE	MSRP	CASH	CREDIT	12 Months	18 Months	24 Months
202391QF	QUEEN/FULL HEADBOARD (WARM BROWN)	Pg.22	$155.99	$135.99	$149.99	$14.99	$10.99	8.99
202391KE	E KING HEADBOARD (WARM BROWN)	Pg.22	$232.99	$203.99	$224.99	$22.99	$16.99	13.99
202391T	TWIN HEADBOARD (WARM BROWN)	Pg.22	$141.99	$123.99	$136.99	$13.99	$9.99	8.99
202392	NIGHT STAND (WARM BROWN)	Pg.22	$134.99	$117.99	$129.99	$12.99	$9.99	7.99
202393	DRESSER (WARM BROWN)	Pg.22	$376.99	$329.99	$362.99	$36.99	$26.99	21.99
202394	MIRROR (WARM BROWN)	Pg.22	$84.99	$73.99	$81.99	$8.99	$5.99	4.99
202395	CHEST (WARM BROWN)	Pg.22	$319.99	$279.99	$307.99	$30.99	$22.99	18.99
	Q 4PC SET (Q/F.HEAD,NS,DR,MR)		$750.99	$656.99	$722.99	$72.99	$52.99	42.99
	Q 5PC SET (Q/F.HEAD,NS,DR,MR,CH)		$1,069.99	$935.99	$1,029.99	$102.99	$74.99	60.99
			MSRP	CASH	CREDIT	12 Months	18 Months	24 Months
202081Q	QUEEN BED (CAPPUCCINO)	Pg.23	$262.99	$229.99	$252.99	$25.99	$18.99	14.99
202082	NIGHT STAND (CAPPUCCINO)	Pg.23	$136.99	$119.99	$131.99	$13.99	$9.99	7.99
202083	DRESSER (CAPPUCCINO)	Pg.23	$387.99	$339.99	$373.99	$37.99	$26.99	21.99
202084	MIRROR (CAPPUCCINO)	Pg.23	$91.99	$79.99	$87.99	$8.99	$6.99	5.99
202085	CHEST (CAPPUCCINO)	Pg.23	$305.99	$267.99	$294.99	$29.99	$21.99	17.99
	Q 4PC SET (Q.BED,NS,DR,MR)		$877.99	$768.99	$845.99	$84.99	$61.99	49.99
	Q 5PC SET (Q.BED,NS,DR,MR,CH)		$1,183.99	$1,035.99	$1,139.99	$113.99	$82.99	66.99

<tbody>
</tbody>

I would like to add a page break between pg.22 and pg.23. The pages run all the way to around 300 pages. Can you please help me, i have tried so many other websites but still cant seem to get it to work. Thank you in advance

 

[VeBeYay]

Hi,
Did you try this?

 

[i8ur4re]

Hi,
Did you try this?

How would i implement that into my excel sheet?

 

[Domenic]

The following macro assumes that the sheet containing the data is the active sheet, and that the data starts at A1. It uses Column D to determine where to add the page breaks. It adds a page break at the row prior to each occurrence of "MSRP".

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> AddPageBreaks()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> rFoundCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> sFirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    ActiveSheet.ResetAllPageBreaks<br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Columns("D")<br>        <SPAN style="color:#00007F">Set</SPAN> rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext, MatchCase:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rFoundCell <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            lCnt = 0<br>            sFirstAddress = rFoundCell.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                lCnt = lCnt + 1<br>                <SPAN style="color:#00007F">If</SPAN> lCnt > 1 <SPAN style="color:#00007F">Then</SPAN><br>                    ActiveSheet.HPageBreaks.Add before:=rFoundCell<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> rFoundCell = .FindNext(rFoundCell)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> rFoundCell.Address <> sFirstAddress<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rFoundCell = <SPAN style="color:#00007F">Nothing</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope this helps!

 

[i8ur4re]

The following macro assumes that the sheet containing the data is the active sheet, and that the data starts at A1. It uses Column D to determine where to add the page breaks. It adds a page break at the row prior to each occurrence of "MSRP".

Option Explicit

Sub AddPageBreaks()

****Dim rFoundCell As Range
****Dim sFirstAddress As String
****Dim lCnt As Long
****
****ActiveSheet.ResetAllPageBreaks
****
****With ActiveSheet.Columns("D")
********Set rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext, MatchCase:=False)
********If Not rFoundCell Is Nothing Then
************lCnt = 0
************sFirstAddress = rFoundCell.Address
************Do
****************lCnt = lCnt + 1
****************If lCnt > 1 Then
********************ActiveSheet.HPageBreaks.Add before:=rFoundCell
****************End If
****************Set rFoundCell = .FindNext(rFoundCell)
************Loop While rFoundCell.Address <> sFirstAddress
********End If
****End With
****
****Set rFoundCell = Nothing
****
End Sub

Hope this helps!

I tried this just now, i got nothing. Would i be able to send you the excel sheet, i have tried everything.

 

[Domenic]

Are my assumptions correct?

Did you make any changes to the code?

Does it help if you replace...

Set rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext, MatchCase:=False)

with

Set rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=[COLOR="#FF0000"]xlPart[/COLOR], searchdirection:=xlNext, MatchCase:=False)

 

[i8ur4re]

Are my assumptions correct?

Did you make any changes to the code?

Does it help if you replace...

Set rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext, MatchCase:=False)

with

Set rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=[COLOR=#FF0000]xlPart[/COLOR], searchdirection:=xlNext, MatchCase:=False)

That still didnt work for me. Im not sure what i am doing wrong. Any other suggestions?

 

[Domenic]

Were my initial assumptions correct?

Did you make any changes to the code? If so, can you post the exact code?

 

[i8ur4re]

Were my initial assumptions correct?

Did you make any changes to the code? If so, can you post the exact code?

I copied what you provided me and pasted it. When i switched over to page break layout, it didnt show the pages broken at the msrp column.

 

[Domenic]

Since you didn't make any changes to the code, I'm assuming that my earlier assumptions were correct. That is, the sheet containing the data is the active sheet, the data starts at A1, and Column D is the "MSRP" column.

This probably won't make a difference, but it's worth trying...

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> AddPageBreaks()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> CurrView <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rFoundCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> sFirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    CurrView = ActiveWindow.View<br>    <br>    ActiveWindow.View = xlPageBreakPreview<br>    <br>    ActiveSheet.ResetAllPageBreaks<br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Columns("D")<br>        <SPAN style="color:#00007F">Set</SPAN> rFoundCell = .Find(what:="MSRP", after:=.Rows(.Rows.Count), LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext, MatchCase:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rFoundCell <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            lCnt = 0<br>            sFirstAddress = rFoundCell.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                lCnt = lCnt + 1<br>                <SPAN style="color:#00007F">If</SPAN> lCnt > 1 <SPAN style="color:#00007F">Then</SPAN><br>                    ActiveSheet.HPageBreaks.Add before:=rFoundCell<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> rFoundCell = .FindNext(rFoundCell)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> rFoundCell.Address <> sFirstAddress<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    ActiveWindow.View = CurrView<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rFoundCell = <SPAN style="color:#00007F">Nothing</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Does this help?