Inserting Page Breaks

[Guest]

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?

 

[Bernard Liengme]

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

 

[Guest]

Bernard,

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

Thanks,
Doug

 

[Gord Dibben]

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