VBA Code needed to automate page break(s) based on 3-column groupings

Bump - No response

Bump - No Response

Admin - please review and let me know whether or not I filed/posted correctly. My original post was on 6/27 and I haven't received any responses yet. Thanks.

This is for Horizontal Page Breaks.
Maybe change it for Vertical Page Breaks.

Please Login or Register  to view this content.

Thanks jolivanes for the reply!

This will work for a designated number of rows, but I don't always know how many columns there will be (sometimes there are single columns as well, which represent a single column discount), to be able to add a vertical page break after that number of columns is reached. I was thinking if I just count the number of "fees" columns, regardless of whether any of these singles columns existed, then I could get the desired page breaks.

Any other suggestions? Thanks for reviewing.

Great - thanks! I will give this a try and let you know how it works for me.

This should work if you set up your sheet so you don't have to fiddle with "sometimes single columns".

Please Login or Register  to view this content.

Thanks jolivanes. This is almost what I need. I really need to figure out how to keep the three column groupings together (Qty/Rate/Amount), since not having them together is not ideal for client viewing. (Note: once all formatting is complete, I save the file to pdf and that is what gets sent to the client.) Below is an update to the code, which includes additional formatting, as well as to horizontal page breaks.

What you have sent over so far is great! Any other ideas on how to incorporate the single discount column(s) into the process?

Please Login or Register  to view this content.

In your example from post#1, I don't see a discount Column anywhere.
Also, now you show in your code that the Title Columns are 4 Columns, not just a single Column.

I omitted that originally because I was thinking the counting of the 3-columns would disregard whether or not a discount column existed. I don't always have discounts for a particular client, so I apologize for leaving that out. As for the title columns, I left out that there were four because I was trying to simplify the example (in your code example, I was able to easily modify and expand to include all 4 title columns.)

Using the constant '1NumberOfColumnsPerSheet' in your sample code and setting that to 15 gets me most of the way there and works fine if the client doesn't have any applicable discounts that month. Thanks again for your help with this problem.

Actually, you don't want to use just

Please Login or Register  to view this content.

because it will take forever to run. Need to limit it. Maybe use

Please Login or Register  to view this content.

Might be some other minor issues. I'll post something more shortly.

Thanks. It definitely took forever to run with the initial code. I modified to use the 'intersect' limitation indicated in your last post, but am now getting a "run-time error '424': Object required" error.

I can't get this to work. It seems that horizontal page breaks and vertical page breaks don't have the same value. I did a bunch of testing and for horizontal page breaks, a soft break = 4105, after setting a hard break, it changes to 4135. For columns, after setting a hard break, it stays at 4105.

The following code should work but unfortunately it doesn't. This is based on your original file posting. Code could be added to make this work but I don't have time now. Just remember where the last soft break was found and continue one column to the left.

Please Login or Register  to view this content.

If you care to look at how page breaks are identified you can use these.

Please Login or Register  to view this content.

Originally Posted by mkhammers

Thanks. It definitely took forever to run with the initial code. I modified to use the 'intersect' limitation indicated in your last post, but am now getting a "run-time error '424': Object required" error.

Maybe try changing the code that sets the page break. I seem to recall having some issues with using the code supplied by the macro recorder. Try

Please Login or Register  to view this content.

If that doesn't fix it then maybe run in debug and find what line fails.

As noted in my previous message though, even modified code will run forever becase once the hard break is added, it still shows up as a soft break so it keeps adding a break to the same column.

Actually, after a little more testing, this actually isn't too bad.

The following will work to force breaks only prior to the Qty column in your original workbook.

Please Login or Register  to view this content.

I removed the page breaks reset but you could put it back in there, just keep in mind that it also resets the print size ratio to 100%. This may or may not work as the hard breaks are set based on the soft breaks so you may want to adjust the print size prior to running.

This is good, but I had to modify the following code to get it to work with Excel 2010. I will continue to wok on modifying the code in order to account for the desired number of 3-column groupings. Thanks to the assistance I have been getting from you and jolivanes, I am almost there!

Change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.