[Solved] [Solved] Printing: Inserting page breaks and other

  • This is driving me insane.
    I have a single worksheet that prints to about six pages and has headings set out on various rows throughout it .. Is there any way of ensuring when I print the worksheet that the headings do not end up as the last line of any individual pages.
    I do not want to insert page breaks as a lot of free text can be entered on this worksheet and that would cause a lot of unwanted white space when printing. I am looking for a VBA fix that will check the last line of data before the end of each page when the worksheet is printed
    Any help gratefully received.

    Thanks in advance

  • Hi scharles, welcome to the Forums.
    The code below deletes all page breaks on the page then recreates them at each line in column A where it finds text "xxx". You can change "xxx", and the "xxx" can even be imbedden in a longer string ie " New Pagexxx number".

    Hope this is what you are looking for. The other approach that I can think of offhand is more complicated and would be for the macro to loop & find the first line with "xxx", define the print area, print, find the next line with "xxx", redefine the area, print etc...loop, loop,loop...

    Sub PageBreaker()
    Cells.PageBreak = xlPageBreakNone
    With Worksheets("sheet1").Range("a1:a500")
    Set c = .Find("xxx", LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Worksheets("Sheet1").Rows(c.Row).PageBreak = xlPageBreakManual
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!