[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
    Do
    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!

Register Yourself Login