Inserting Page Breaks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blasds78 - 22 May 2007 13:46 GMT

I'd like to know if there's an easy way to insert page breaks into my
spreadsheet. I have a list in column B. Some of the items in the colums a
listed more than once. I've sorted the list in ascending order and now I'd
like to insert a page break between each individual item.

Is there a way to accomplish this with some formula (or other method), or do
I have to manually insert a page break after each item?

Reply to this Message

Bernard Liengme - 22 May 2007 14:16 GMT

This VBA subroutine seems to work

Sub breakup()
ActiveWindow.View = xlPageBreakPreview
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
mytest = Range("B1").Value
For Each mycell In Selection
If mycell.Value <> mytest Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=mycell
mytest = mycell.Value
End If
ActiveWindow.View = xlNormalView
Next
End Sub

Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> I'd like to know if there's an easy way to insert page breaks into my
> spreadsheet. I have a list in column B. Some of the items in the colums
[quoted text clipped - 6 lines]
> do
> I have to manually insert a page break after each item?

Reply to this Message

blasds78 - 22 May 2007 14:42 GMT

Bernard,

Thanks for your help. I'm sure that works, but I'm too much of a novice to
understand those commands.

Thanks,
Doug

> This VBA subroutine seems to work
>
[quoted text clipped - 26 lines]
> > do
> > I have to manually insert a page break after each item?

Reply to this Message

Gord Dibben - 22 May 2007 15:57 GMT

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

First...create a backup copy of your original workbook.

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

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

You can also assign this macro to a button or a shortcut key combo.

Gord

>Bernard,
>
[quoted text clipped - 34 lines]
>> > do
>> > I have to manually insert a page break after each item?

Reply to this Message