Inserting Page Breaks

[chrisnelsonusa1]

I have a worksheet that in column "S" I have written a simple equation
that checks column "R" to see if a value is repeated. When it comes to
an instance where the cell in column "R" does not repeat, it inputs
"Insert Page Break" in the affected cell in column "S".

Is there a way to automatically insert a page break at the cell that
"Insert Page Break" occurs and continue down the worksheet until it
finds the same instruction again?

You can see an example attached.

Chris Nelson


+-------------------------------------------------------------------+
|Filename: test1.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4108 |
+-------------------------------------------------------------------+

 

[Gord Dibben]

Chris

Instead of using a formula to show "insert page break" you can dispense with
column S and run a macro on Column R directly to insert a page break at each
change of value in column R,

Sub InsertBreak_At_Change()
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.


Gord Dibben Excel MVP

 

[chrisnelsonusa1]

I have to tell you, this web site makes me a hero every time here a
work. Thanks so much for the help and the macro, you just saved someon
at least two hours of monotony every week.

Chri

 

[Gord Dibben]

Thanks for the feedback Chris.

That's what macros are all about.....relieving the drudgery of repetitive
tasks.


Gord