+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    Hello. This is my first post, so hopefully I use the correct process.

    I have an Excel file (I use Excel 2010) with a series of 3-column groupings (Qty/Rate/Amount - see Sample file) which I would like to remain together in the event the content is large enough to cover multiple pages (i.e. I don't want Qty & Rate to be on page 1 and Amount for that same service to move over to page 2, for example.) I would also like to be able to insert the vertical page break(s) at the desired location so that the content of each page is large enough to be easily viewed once the file is saved as a PDF file. Based on the usual amount of content in these files, that amount to roughly 5-6 of these groupings per page.

    My thinking was to first remove all automatic page breaks. Then I would have the code count the number of occurrences of the word "Amount" and insert a manual vertical page break after the count reaches 5, at which time the count would be reset and the process would start over again until the next page break was inserted. This process would continue until the end of the file. Of course, I need also need a calculation to know when the file ends and that I can terminate the 5-count process described above.

    In the sample file, there is currently an automatic vertical page break between columns W and X. Using the scenario described above, I would want to end up with a manual page break between columns P and Q. [As an alternative, I am happy to move the automatic page breaks to the desired location, but am not sure how to do that either.] This would be the only page break in the example, since the next one is the end of the file.

    Any help would be appreciated. Thanks.
    Attached Files Attached Files
    Last edited by mkhammers; 07-01-2013 at 04:23 PM. Reason: clarified my summary of the issue

  2. #2
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    Bump - No response

  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    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.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,296

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

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


    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    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.

  6. #6
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    I've done a similar thing with horizontal page breaks. Assuming you just need some suggestions on how to go about it and you can write your own code...


    First, set the print magnification percentage to something you feel is readable.


    Loop through all cells in row 3 (if this may change you may need to find the proper row).

    As you loop through, set a variable, BreakCol, to any column that contains "Qty". This will constantly change but will always hold the column of the most recent column that is allowed to have a break.

    As you loop through, use .EntireRow.PageBreak = xlPageBreakAutomatic to find the any auto page breaks. When you find an auto page break, set a manual page break at the column contained in BreakCol.


    Have a boolean variable to track whether an automatic page break was found and put that all inside a do loop to keep running as long as an automatic page break is found.


    for example (not real code, just to show basic structure)


    BreakFound = true
    do while breakfound = true
    BreakFound = false
    for each Item in row3.cells
    if item.text = "qty" then set BreakCol = Item.entirecolumn
    if item.entirecolumn.pagebreak = xlautomatic then
    breakfound = true
    add a page break at BreakCol
    exit for
    end if
    next
    loop

    That might be a little over simplified but i think that's all it really takes. You may need to consult the help menu for some of the syntax required as this isn't real code, it's just some basic junk to show concept.
    Last edited by Cyclops; 07-08-2013 at 04:30 PM.

  7. #7
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    Actually, there's no need for the do loop. Code is much simpler without it.


    for each Item in row3.cells
    if item.text = "qty" then set BreakCol = Item.entirecolumn
    if item.entirecolumn.pagebreak = xlautomatic then
    add a page break at BreakCol
    End if
    next
    That should work. Seems too simple though.



    EDIT:

    Ran a quick test and it seems like the Do Loop may be required. Running it through a straight loop doesn't work because it doesn't reset subsequent page breaks so it finds the soft breaks where they were originally, not where they are after setting the hard break/s.

    Also, it seems that excel counts the left most cell of the print range as having a soft break so you may need to make sure you skip that cell. Or I guess it wouldn't really matter.
    Last edited by Cyclops; 07-08-2013 at 05:01 PM.

  8. #8
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

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

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,296

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

    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.

  10. #10
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    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.

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,296

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

    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.

  12. #12
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    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.

  13. #13
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    Thanks Cyclops. I modified the code I posted in response to jolivanes and tried to incorporate your suggestion, but am still not getting it to work as needed. I think I am still missing code that will add the page break after 5 occurrences of 'Amount' are found (or the last column is reached.) Thanks for your assistance as well!

    Please Login or Register  to view this content.
    Last edited by mkhammers; 07-09-2013 at 11:26 PM. Reason: correction

  14. #14
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    Try changing

    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Also make sure the case of the text matches.

    EDIT:

    There's also a problem with the break column. My fault, my early posting showed Item.EntireColumn but that's not required, you really only need a single cell so change to


    Please Login or Register  to view this content.

    I would probably change the name of BreakCol to BreakCell and dim it as a range.
    Last edited by Cyclops; 07-10-2013 at 09:42 AM.

  15. #15
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    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.

  16. #16
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    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.

  17. #17
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    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.

  18. #18
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    Quote Originally Posted by mkhammers View Post
    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.

  19. #19
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003 & 2010 Access 2010
    Posts
    103

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

    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.

  20. #20
    Registered User
    Join Date
    04-22-2013
    Location
    Overland Park, KS
    MS-Off Ver
    Office 2010
    Posts
    12

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1