VBA to insert page breaks

Hi there,

I am using the following code courtesy of xladept @: http://www.excelforum.com/excel-prog...int-macro.html

Sub SetPA():
Dim i As Integer
Dim t As Integer
Dim PA As String

t = 2
i = Range("C" & Rows.Count).End(xlUp).Row: PA = "$A$1:$J" & i
t = Int(i / 50) + 1
With ActiveSheet.PageSetup
         .PrintArea = PA: .Orientation = xlPortrait
        .CenterHorizontally = True: .Zoom = False
        .FitToPagesWide = 1: .FitToPagesTall = t
End With: End Sub

This macro simply inserts page breaks into the active worksheet.

I need to be able to change the position of these page breaks such that my sheet prints more neatly!

1. The current position of all the horizontal page breaks needs to be adjusted either up or down depending on the following.

From the Horizontal Page break Row - Find what the Row number is of the next cell ABOVE the page break row & in Column 2, thats cell.value is like "*Crew*"
Likewise do the same for BELOW who's cell value is also like "*Crew*"

Determine which of these is the closer to the page break, and re-adjust the page break to the position of the found cell/row.

This needs to be done for all the Page breaks on the worksheet

Some help on this one would be really appreciated!

Regards
Jordan

Hello jordan2322,

Have you thought of using Word instead of Excel? It is far more flexible for page formatting than Excel is.