Inserting page breaks in excel 2011 with VBA

I am trying to insert page breaks in a worksheet in Excel with VBA.  (code attached)

The sheet has 4 columns, but only columns A and B are important here.  Column A has an alphabetic list of Artists and for each Artist, column B has a list of Songs for that Artist.  The data is divided logically into alphabetical segments with a header row (e.g. "A     B     C", "D     E     F").  Here is an example of the data:

A     B     C
Artist                            Song
Adele                        Hometown Glory
                                Make You Feel My Love
                                One And Only
                                Someone Like You
Albert E. Brumley        I'll Fly Away
Alison Krauss        A Living Prayer
                                Baby, Now That I've Found You
                                But You Know I Love You
                                Forget About It
                                Ghost In This House
                                Oh, Atlanta
                                Stay
                                When You Say Nothing At All
Amos Lee                Arms Of A woman
                                Black River

Whenever Excel's natural page break lands in the middle of a song list, I want to back up and force a page break just before the name of the artist in column A so that the artist and all associated songs start at the top of the next page.  Likewise, if the last row on a page is one of the header rows, I want to bump that row to the top of the next page.

My code does this nicely, but only when I am in debug mode and slowly stepping through it.  If I get out of debug mode and "let 'er rip" the code works for the first case where I insert a page break but then does not work for the remaining situations where it inserts a page break.

In a post somewhere (cannot remember the web site or post topic) someone suggested that when you insert a page break, Excel communicates with the printer.  Thus, when I am stepping slowly through the code in debug mode, there is plenty of time for that.  But when I run the code normally, there is not enough time to accomplish that for the newly inserted page break before the code races along to handle the next situation.

I have tried inserting "Do Events" and "Wait" instructions (e.g. wait 10 seconds), but neither of them has any effect.

Is there any better way to insert page breaks?  (That will be better, but hopefully not super slow.)
Insert-Page-Break-Code.rtf
LVL 1
wsturdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
First of all, nice selection of music!  I fired up my Alison Krauss playlist while working on this.. :-)

I had to add several more rows of example artists and titles to test.  When I only had two pages of data, it ran fine in real-time.  I had an issue after I added third page of data, in which case it only adjusted the first page break but not the second.  

So I added a Wait method of just one second and that seems to work.  See the attached example file with this section of modified code at the bottom:
        If currRow <> origRow Then
            rng.EntireRow.Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
            Application.Wait (Now + TimeValue("0:00:01"))
        End If

Open in new window


-Glenn
EE-TestPageBreaks.xlsm
0
wsturdevAuthor Commented:
As I indicated in my original question I tried the Wait method also and it did not work.

My actual data is 254 rows long.  You might have had different results with that much data.  I have attached a sample file with the full data in it.

I have also done more testing:

I checked to see how many page breaks there are by putting this instruction immediately after changing to xlPageLayoutView:
Z = ws.HPageBreaks.Count .  Z was 6 after I executed that statement.

Then, after the loop, but before changing the view back to xlNormalView I inserted the same statement.  Now Z was 8!!

Then I changed the view back to xlNormalView and ran that same statement again and Z was now back to 6!

Somehow, inserting the new breakpoints (which are intended to take the place of existing ones) the loop gets screwed up.
Insert-Page-Break-Sample-Data.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
I updated my example workbook with your sample data and could replicate your issue.

I then changed your ActiveWindow.View commands to use the xlPageBreakPreview parameter instead of xlPageLayoutView.  This seemed to work better, so I added a pair of lines after the page break insertion that flips from normal to page break and all breaks look correct.  Finally, I turned off screen updating to speed it up a bit.

See the updated workbook and run "Fix_Breaks" to call the subroutine.

-Glenn
EE-TestPageBreaks.xlsm
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

wsturdevAuthor Commented:
When I run:
    ActiveWindow.View = xlPageBreakPreview
I get a 1004 error. "Method 'View' of object 'Window' failed."

The 3 options for ActiveWindow.View are xlNormalView, xlPageLayoutView and xlPageBreakPreview, so I am not sure why I am getting the error.

Also, I keep forgetting this might make a difference, but I am using Excel 2011 for Mac.

Bill
0
wsturdevAuthor Commented:
I just found this on stackoverflow.com:

Although the VBA editor for Mac has the xlPageBreakPreview property it won't be able to execute because there is no page break preview mode in Excel 2011. That is why this will always return an error when executed within Excel 2011.

And it is true, the only view options on the View Menu are "Normal" and "Page Layout".
0
Glenn RayExcel VBA DeveloperCommented:
Sorry; I don't have an answer for you there.  I was testing with Excel 2010 (Windows).
0
wsturdevAuthor Commented:
I may be forced to reset the page breaks (turn them off) and then loop through adding up the row heights until I find a total height that is appropriate and then back up to the proper row if necessary and set a break.

Right now I cannot think of another way.
0
Glenn RayExcel VBA DeveloperCommented:
I'm inclined to agree with you.  You'll just need to estimate the maximum page height printable - including the header row - and then do a running total while checking the value in the Artist column.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wsturdevAuthor Commented:
I am thankful you have attempted to help me and will accept your last response as the "solution".  Once I figure out a routine as indicated, or if I discover more information that provides a solution, I will come back here and post it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.