VBA to insert page breaks

[jordan2322]

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