auto format page breaks

[rwynn]

Has anyone auto formatted page breaks based on column data. I have part numbers associated to bin numbers and want to make a new page for each change in the bin number.

thank you

rw

 

[Joe Was]

Sub SetPageBreaksOnColA()
'Break on every value change.
Dim rng As Range, pageRows#, totalRows#, n#
Dim strLastValue As String
Dim strCurrValue As String

'Set rng = Range([A1], Cells.SpecialCells(xlCellTypeLastCell))
totalRows = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
n = 1
' turn off automatic page breaking
Worksheets("Sheet1").Rows(totalRows).PageBreak = xlPageBreakNone
strLastValue = Cells(1, 1).Value 'store first row value

Do Until n > totalRows
strCurrValue = Cells(n, 1).Value 'store first row value

If strCurrValue <> strLastValue Then
ActiveSheet.HPageBreaks.Add Before:=Rows
strLastValue = strCurrValue
End If
n = n + 1
Loop
End Sub