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.

Sub GenerateGuide()
'
' GenerateGuide Macro
'

'
    Sheets("Interview Guide Builder").Select
    Sheets("Interview Guide").Visible = True
    Sheets("Interview Guide").Select
    Sheets("Interview Guide").Copy After:=Sheets(2)
    Sheets("Interview Guide").Select
    ActiveWindow.SelectedSheets.Visible = False
    Columns("B:B").Select
    Selection.AutoFilter
    ActiveSheet.Range("$B:$B").AutoFilter Field:=1, Criteria1:="0"
    Cells.Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("C2").Select
    Selection.Copy
    Range("H2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=""Interview Guide ""&RC[6]&"" ""&TEXT(NOW(),""ddmmyyyy hhmm"")"
    Range("B2").Select
    Selection.Copy
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Environ("USERPROFILE") & "\Desktop\" & Range("G2").Value & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    Application.DisplayAlerts = False
    Sheets("Interview Guide (2)").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets("Interview Guide Builder").Select
End Sub

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.