Inserting & Removing Page Breaks

Hi Matthew,

You can probably add the page breaks in your existing code. I would suggest finding the row you want for the page break (e.g. 'myRow = r.row' or' myRow = r.row + 3').

Please Login or Register  to view this content.

I hope the above helps.

Lewis

Lewis,

Thanks for getting back to me this morning. I've tried my hand at adding the recommended code to my existing code for the AddIHSS and I keep coming up short (errors). It's likely less about your code and more about my lack of knowledge as to the correct placement of the code. Would you be willing to point me in the right direction as to where you were thinking the recommended code might be placed in my code?

Also, I'm thinking that if I I reset all page breaks in the worksheet won't I be undoing what I've previously done each time I add a new range? Thanks again for your help.

Matthew

Lewis & John,

I know better than to not included a sample of the workbook I'm working on when I post a question.....sorry about that. Here you go. I've highlighted where I'm attempting to insert page breaks with yellow/grid pattern.

Matthew

P.S. Password for the sheets is "j" and if the button disappear hit ctrl + r and they will reappear.

Hi Matthew,

Your sample workbook was very helpful. Try the following (which places the 'Page Break' after the yellow cross hatching). Changes are in blue:

Please Login or Register  to view this content.

The 'Delete' is probably not needed in your application, as you are adding data at the end of the sheet, in this routine.

Lewis

Lewis,

Your suggested modifications work great. I'm still running it through it's paces and will let you know how it turns out.

John,

I noticed that in your suggestion is "resetting" the page breaks each time the sub runs. Does this prevent extra page breaks from "building up" when new ranges are added and then deleted? Also, I presume the sub routine will need to be called when I'm adding or deleting a new IHSS or CS?

Thanks guys for offering up suggestions.

Matthew

John & Lewis,

I've attached copies of the workbook, one with Lewis' code (V1.99) and the other with John's code (V2.00). Both appear to be behaving identically and perfectly...I might add. I'll run some real data through the workbooks at let you know how things go.

Neither of the codes appears to be adding additional page breaks or pages when new ranges are added and old ones are deleted. My quick assessment of the two suggested ways of adding page breaks is:

If all worksheets have a universal place holder (i.e. "Total Hours") then John's code would only need to be added to the workbook once and it would apply to all worksheets...making the process of adding page breaks very effective and efficient. However, if each worksheet requires unique place holders OR the user only wants page breaks on certain worksheets then Lewis' code would be allow for the individualization that would be required.

Thanks again.

Matthew

John,

I just uploaded V2.08 again as I think possibly my previous attempt to upload the form did not work as expected. Thanks.

Matthew

John,

I see your point of ordering the code to fire in the correct order and I've now tried a couple different variations with the following looking to be the most promising but alas I'm still coming up short:

Please Login or Register  to view this content.

How is it the code places a page break between the first and second ranges but then ranges thereafter? Hmmmm. Thanks.

Matthew