Inserting A row & page break

[Jamie]

Hello

I have data set up like this:

brand POLTRM devmth _NAME_ _2003 _2004 _2005
AD 10 1 bincear 21.8% 18.0% 22.6%
AD 10 2 bincear 16.7% 21.0% 14.6%
AD 10 3 bincear 16.8% 23.9% 14.3%
AD 10 4 bincear 18.5% 23.1% 15.1%
AD 10 5 bincear 19.4% 22.9% 17.7%
AD 12 1 bincear 19.9% 24.3% 18.7%
AD 12 2 bincear 20.4% 22.7% 18.3%
AD 12 3 bincear 19.8% 22.4% 18.3%
AD 12 4 bincear 19.4% 21.8% 18.3%
AD 12 5 bincear 19.6% 21.1% 19.7%

I am trying to set up a macro whereby each time the devmth column drops back
to 1 or is lower than the previous row I would like to insert 5 blank rows
and a page break. I have not had any luck with this at all yet. Can anyone
help.

I would be extremely grateful for any assistance.

Thanks in advance

Jamie

 

[Tom Ogilvy]

Assume the devmth column is column C:

Sub CCC()
Dim lastRow as Long, i as Long
lastrow = cells(rows.count,"C").End(xlup).row
for i = lastrow to 2 step -1
if cells(i,3) < cells(i-1,3) or cells(i,3) = 1 then
cells(i,3).EntireRow.Resize(5).Insert shift:=xlShiftDown
' pagebreak below the 5 blank rows?
Activsheet.HPageBreaks.Add Before:=Rows(i+5)
end if
Next
end Sub

to put the pagebreak at the top of the blank rows, change rows(i+5) to rows(i)