Help! How do I insert a manual page break on a change in cell contents

A

Al Dykes

If I sort spreedshet on Column A, I want to print a paper report that
starts a new page whenever the contents of col. A changes.

How?

TIA.
 
M

Mike H

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub marine()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A2:A" & lastrow)
For Each c In myrange
c.Select
If c.Value <> c.Offset(-1, 0).Value Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End If
Next
End Sub

Mike
 
G

Gord Dibben

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("A1")
For Each rng In Range("A1:A300") '<< change range
If rng.text <> OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP
 
Top