Inserting pagebreaks into an Excel Worksheet

[cs_vision]

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!

cs_vision wrote:
>
> 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

[cs_vision]

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.

"Dave Peterson" wrote:

> 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!
>
> cs_vision wrote:
> >
> > 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
>

[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

On Tue, 17 May 2005 15:36:04 -0700, cs_vision
<[email protected]> wrote:

>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.
>
>"Dave Peterson" wrote:
>
>> 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!
>>
>> cs_vision wrote:
>> >
>> > 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
>>

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



cs_vision wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > 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!
> >
> > cs_vision wrote:
> > >
> > > 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
> >

--

Dave Peterson

[cs_vision]

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

"Gord Dibben" wrote:

> 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
>
> On Tue, 17 May 2005 15:36:04 -0700, cs_vision
> <[email protected]> wrote:
>
> >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.
> >
> >"Dave Peterson" wrote:
> >
> >> 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!
> >>
> >> cs_vision wrote:
> >> >
> >> > 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
> >>
>
>

[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!)

cs_vision wrote:
>
> If I only need to sort by a certain column and only the first changing value
> of that column, will this work?
>
> "Gord Dibben" wrote:
>
> > 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
> >
> > On Tue, 17 May 2005 15:36:04 -0700, cs_vision
> > <[email protected]> wrote:
> >
> > >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.
> > >
> > >"Dave Peterson" wrote:
> > >
> > >> 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!
> > >>
> > >> cs_vision wrote:
> > >> >
> > >> > 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
> > >>
> >
> >

--

Dave Peterson