VBA/Macro to detect a heading at the bottom of a page and create a page break

Hi everyone

First up, sorry about the long post, I thought I would write down a bit of a background of how the code works etc. before I post my problem.
I have created an Interview Guide Builder that will be used by people to choose from a list of questions and write some of their own to ask candidates who are applying for jobs.
I have one macro within this document, titled “GenerateGuide”. It generates a pdf Interview Guide that the interviewer will take with them the meeting.

Please Login or Register  to view this content.

The way I have set it up to run is:

- The user selects / writes the question and presses the “Generate Guide” button which starts the macro
- All questions appear in a hidden sheet titled “Interview Guide” and have a formula in column B which decides whether the question has been selected or not.
- The macro unhides the hidden “Interview Guide” worksheet and makes a copy of it. The original “Interview Guide” is then re-hidden.
- It then filters columns B to only show those which were not selected. It then deletes these rows.
- Column B is then deleted.
- To create an individual title for each time the Guide is generated, the macro then creates the title which ends up in cell G2.
- The guide is then published as a PDF (as I can’t copy and paste into word and keep the formatting nice).
- The duplicate worksheet is then deleted. End macro.

The macro itself is probably a bit clunky , but I’m a bit of a novice and so I’m happy with what it can do so far (if anyone has whiz bang ideas to make it run smoother, by all means let me know!!).

What I am having trouble with is this… In the example I have attached, you will see that I have pre-selected some questions etc to help show my issue.
When I run the macro and it publishes in pdf, you will notice some of the grey headings eg. “Managing Services” and “Leads and Empowers” sit at the bottom of two pages and the questions meant for that section are then on the next page. This will change depending on the questions selected by the user though, but happens quite frequently. What I want is that if a new heading comes up, then the first question under that heading appears straight after it (ie. On the same page) – does that make sense?
I’ve tried resizing rows etc, but no luck.

I have a few thoughts on how this could be fixed, but no idea how to do it:

- As part of the macro, if it notices a heading at the bottom of a page, it creates a page break to push it over to the next page (probably preferred option).
- A word document is created (keeping the formatting as it is set up in the excel file) and the user can press enter to push the headings down to the next page themselves if they notice it.

Any ideas?

Cheers
Jess.

Hi John and Natefarm

Both appear to work perfectly!
I'm just getting my team to test it with me (so that we can get a bunch of combinations to check the headings are pushing down where needed), but thank you so much in advance!
I'll let you know if there are any issues, but at this point, I'm happy to mark as solved.

Cheers
Jess.