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
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
When You Say Nothing At All
Amos Lee Arms Of A woman
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.)