Inserting Page Breaks
 

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 |
+-------------------------------------------------------------------+

--
chrisnelsonusa1
------------------------------------------------------------------------
chrisnelsonusa1's Profile: http://www.excelforum.com/member.php...o&userid=29526
View this thread: http://www.excelforum.com/showthread...hreadid=492279

Inserting Page Breaks
 

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 InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP



On Fri, 9 Dec 2005 13:18:33 -0600, chrisnelsonusa1
<chrisnelsonusa1.1zserb_1134156001.6008@excelfor um-nospam.com wrote:


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 |
+-------------------------------------------------------------------+

Inserting Page Breaks
 

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

Chris


--
chrisnelsonusa1
------------------------------------------------------------------------
chrisnelsonusa1's Profile: http://www.excelforum.com/member.php...o&userid=29526
View this thread: http://www.excelforum.com/showthread...hreadid=492279

Inserting Page Breaks
 

Thanks for the feedback Chris.

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


Gord

On Fri, 9 Dec 2005 15:02:53 -0600, chrisnelsonusa1
<chrisnelsonusa1.1zsjum_1134162602.947@excelforu m-nospam.com wrote:


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

Chris