Page break for condition

I

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
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.


ITEMDESCRIPTIONPAGEMSRPCASHCREDIT12 Months18 Months24 Months
202391QFQUEEN/FULL HEADBOARD (WARM BROWN)Pg.22$155.99$135.99$149.99$14.99$10.998.99
202391KEE KING HEADBOARD (WARM BROWN)Pg.22$232.99$203.99$224.99$22.99$16.9913.99
202391TTWIN HEADBOARD (WARM BROWN)Pg.22$141.99$123.99$136.99$13.99$9.998.99
202392NIGHT STAND (WARM BROWN)Pg.22$134.99$117.99$129.99$12.99$9.997.99
202393DRESSER (WARM BROWN)Pg.22$376.99$329.99$362.99$36.99$26.9921.99
202394MIRROR (WARM BROWN)Pg.22$84.99$73.99$81.99$8.99$5.994.99
202395CHEST (WARM BROWN)Pg.22$319.99$279.99$307.99$30.99$22.9918.99
Q 4PC SET (Q/F.HEAD,NS,DR,MR)$750.99$656.99$722.99$72.99$52.9942.99
Q 5PC SET (Q/F.HEAD,NS,DR,MR,CH)$1,069.99$935.99$1,029.99$102.99$74.9960.99
MSRPCASHCREDIT12 Months18 Months24 Months
202081QQUEEN BED (CAPPUCCINO)Pg.23$262.99$229.99$252.99$25.99$18.9914.99
202082NIGHT STAND (CAPPUCCINO)Pg.23$136.99$119.99$131.99$13.99$9.997.99
202083DRESSER (CAPPUCCINO)Pg.23$387.99$339.99$373.99$37.99$26.9921.99
202084MIRROR (CAPPUCCINO)Pg.23$91.99$79.99$87.99$8.99$6.995.99
202085CHEST (CAPPUCCINO)Pg.23$305.99$267.99$294.99$29.99$21.9917.99
Q 4PC SET (Q.BED,NS,DR,MR)$877.99$768.99$845.99$84.99$61.9949.99
Q 5PC SET (Q.BED,NS,DR,MR,CH)$1,183.99$1,035.99$1,139.99$113.99$82.9966.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
 
Sort by date Sort by votes
Check to make sure that you've copied the complete code...
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Domenic said:
Check to make sure that you've copied the complete code...
Click to expand...

Ok that worked, the pages are all filled in. But as for the Items in column A, its just showing A and B, the first set of items worked, but the remaining only show A and B.
 
Upvote 0
As a test, try entering each of the following in the Immediate Window, which should return the address and value for the first cell of the second set of items...

Code: 
? Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas(2).Cells(1).Address

? Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas(2).Cells(1).value

Do these return the expected results?
 
Upvote 0
Domenic said:
As a test, try entering each of the following in the Immediate Window, which should return the address and value for the first cell of the second set of items...

Code: 
? Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas(2).Cells(1).Address

? Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas(2).Cells(1).value

Do these return the expected results?
Click to expand...

I recieved a run-time error '1004':
Application-defined or object-defined error
 
Upvote 0
i8ur4re said:
I recieved a run-time error '1004':
Application-defined or object-defined error
Click to expand...


I also have another issue,

202391QFQUEEN/FULL HEADBOARD (WARM BROWN)Pg.22$155.99$135.99$149.99$14.99$10.998.99
202391KEE KING HEADBOARD (WARM BROWN)Pg.22$232.99$203.99$224.99$22.99$16.9913.99
202391TTWIN HEADBOARD (WARM BROWN)Pg.22$141.99$123.99$136.99$13.99$9.998.99
202392NIGHT STAND (WARM BROWN)Pg.22$134.99$117.99$129.99$12.99$9.997.99
202393DRESSER (WARM BROWN)Pg.22$376.99$329.99$362.99$36.99$26.9921.99
202394MIRROR (WARM BROWN)Pg.22$84.99$73.99$81.99$8.99$5.994.99
202395CHEST (WARM BROWN)Pg.22$319.99$279.99$307.99$30.99$22.9918.99
202391QFAQ 4PC SET (Q/F.HEAD,NS,DR,MR)Pg.22$750.99$656.99$722.99$72.99$52.9942.99
202391QFBQ 5PC SET (Q/F.HEAD,NS,DR,MR,CH)Pg.22$1,069.99$935.99$1,029.99$102.99$74.9960.99

<colgroup><col><col><col><col span="2"><col span="3"><col></colgroup><tbody>
</tbody>

The A column basically copies cell A2 to create the last two missing cells in column A, i was hoping we can copy the first 5 numbers and add a letter or number, Maybe a number would be better to show if its a 4 pc set or 5 pc set. So the last two highlighted cells would look like so: 20239-4 and 20239-5 instead of 202391QFA and 202391QFB. Thank you
 
Upvote 0
i8ur4re said:
I recieved a run-time error '1004':
Application-defined or object-defined error
Click to expand...

Column A contains the item number, correct?

The item numbers are made up of text values and numeral values. Are these values constants? Or are they the result of formulas?

Also, let's see whether those blank cells in Column A are really blank. What does the following return when entered in the Immediate Window...

Code: 
? application.countblank(range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))

The A column basically copies cell A2 to create the last two missing cells in column A, i was hoping we can copy the first 5 numbers and add a letter or number, Maybe a number would be better to show if its a 4 pc set or 5 pc set. So the last two highlighted cells would look like so: 20239-4 and 20239-5 instead of 202391QFA and 202391QFB.
Click to expand...

Sure, no problem...
 
Upvote 0
Domenic said:
Column A contains the item number, correct?

The item numbers are made up of text values and numeral values. Are these values constants? Or are they the result of formulas?

Also, let's see whether those blank cells in Column A are really blank. What does the following return when entered in the Immediate Window...

Code: 
? application.countblank(range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))



Sure, no problem...
Click to expand...

The A columns are text values and numeral values like you said, they are not consistent. An example:

MSRPCASHCREDIT12 Months18 Months24 Months
300261QQUEEN BED (CAPPUCCINO)Pg.24$225.99$197.99$217.99$21.99$15.9912.99
300261KEE KING BED (CAPPUCCINO)Pg.24$316.99$277.99$305.99$30.99$22.9917.99
300261KWC KING BED (CAPPUCCINO)Pg.24$316.99$277.99$305.99$30.99$22.9917.99
200422NIGHT STAND (CAPPUCCINO)Pg.24$148.99$129.99$142.99$14.99$10.998.99
200423DRESSER (CAPPUCCINO)Pg.24$421.99$369.99$406.99$40.99$29.9923.99
200424MIRROR (CAPPUCCINO)Pg.24$91.99$79.99$87.99$8.99$6.995.99
200425CHEST (CAPPUCCINO)Pg.24$319.99$279.99$307.99$30.99$22.9918.99
Q 4PC SET (Q.BED,NS,DR,MR)Pg.24$886.99$776.99$854.99$85.99$61.9950.99
Q 5PC SET (Q.BED,NS,DR,MR,CH)Pg.24$1,206.99$1,055.99$1,161.99$116.99$83.9967.99

<colgroup><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>


Basically I would like to use the Main number in this set, which is the 300261. The other numbers are pieces that will fit that set (Dresser, Mirror, Chest, etc). The last two blank cells that are a 4PC and a 5PC would be 30026-4 and 30026-5.

A few items also have Item numbers (what column A are referred too) that have only 4 digits. Like so:

4361ROUND DINING TABLE (NATURAL)Pg.294$225.99$197.99$217.99$21.99$15.9912.99
4127SIDE CHAIR (NATURAL)Pg.294$63.99$55.99$61.99$6.99$4.993.99
4118BENCH (NATURAL)Pg.294$118.99$103.99$114.99$11.99$8.996.99
4361-66 PC SET (TBL+4CHR+BENCH)Pg.294$599.99$524.99$577.99$57.99$41.99
33.99

<colgroup><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

In this situation I used the first number, which represents the bulk of the package.
They are not consistent as you can see.

Also i know we went over this with the page break question, I was wondering if we can do each page group as one page, so pg. 294 would have a page break for all of the items listed as pg.294, we had it originally setup where a page break is inserted after each MSRP column heading. Would we be able to do that?
 
Upvote 0
Domenic said:
Column A contains the item number, correct?

The item numbers are made up of text values and numeral values. Are these values constants? Or are they the result of formulas?

Also, let's see whether those blank cells in Column A are really blank. What does the following return when entered in the Immediate Window...

Code: 
? application.countblank(range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))



Sure, no problem...
Click to expand...

After entering this into the immediate window i received this:

? application.countblank(range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))
1050
 
Upvote 0
Domenic said:
Here's a sample workbook for you to download (File > SaveAs > Download)...

http://1drv.ms/1Frvlib
Click to expand...

Ok. Sorted that out. Thank you

Another thing i was hoping to do was to create a cell that would change the values for that specific column, i have 3 columns that look like this:

Freight Half Number Credit %
Cost
$9.52$38.76$11.70
$14.28$58.14$17.50
$8.68$35.34$10.70
$8.26$33.63$10.10
$23.10$94.05$28.30
$5.18$21.09$6.40
$19.60$79.80$24.00
$46.06$187.53$56.30
$65.66$267.33$80.20

<colgroup><col><col><col></colgroup><tbody>
</tbody>


these all have a formula within them. I would like to create a cell for each one like so:

Freight CostHalf NumberCredit %
14%50%10%

<tbody>
</tbody>


These three cells would basically control the 3 columns. I just want that one row and 3 columns to be able to change the amount in the other fields.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

Share this page

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