Inserting pagebreaks into an Excel Worksheet

[Guest]

Can I automatically insert pagebreaks at certain points in a Excel Worksheet.
I have a worksheet that contains a column where there are unit numbers and I
need to insert a pagebreak whereever the unit number changes.

 

[Dave Peterson]

I find the easiest thing to do is to use Data|Subtotals.

There's an option to put each group on its own page--and I get subtotals on top
of it!

 

[Guest]

The problem is I have over 3900 records in my database and I need to insert
manual pagebreaks for each change unit numbers. There is a limitation with
pagebreaks.

 

[Gord Dibben]

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

Select the 3900 + rows and run the above.


Gord Dibben Excel MVP

 

[Dave Peterson]

You can have 1026 horizontal pagebreaks per sheet.

If you need more than that, then you can't use Data|Subtotals. But that limit
occurs if you add them by hand or by using code like Gord posted.

Maybe you could copy data to different worksheets, put in the pagebreaks there
(how ever you decide), print those sheets and then delete those sheets.


And if you really have mostly 3-4 rows per group, maybe a separate sheet really
isn't the best use of paper.

You could double up the size of the rows to make it look like each group is
double spaced from the previous group.

 

[Guest]

If I only need to sort by a certain column and only the first changing value
of that column, will this work?

 

[Dave Peterson]

Select the cells in that certain column and then try it. If it doesn't work the
way you want, then close without saving.

(I'm betting that if you don't exceed the number of horizontal pagebreaks per
sheet, it'll work fine!)

If I only need to sort by a certain column and only the first changing value
of that column, will this work?