Page Breaks based on cell data

[Josh Craig]

Hi,

I was wondering if anyone knew how to create a macro to insert a page break
after a number of values in a column. For example:

Column A
Category
Cat
Cat
Cat
Dog
Dog
Sheep
Sheep
Sheep

I want to insert a page break in between cat and dog and in between dog and
sheep. Any ideas?

[Dave Peterson]

I'd use data|subtotals.

There's an option to insert page breaks between groups on that dialog.

And I could even get subtotals (counts, sums, averages,...) if I wanted them.

Josh Craig wrote:
>
> Hi,
>
> I was wondering if anyone knew how to create a macro to insert a page break
> after a number of values in a column. For example:
>
> Column A
> Category
> Cat
> Cat
> Cat
> Dog
> Dog
> Sheep
> Sheep
> Sheep
>
> I want to insert a page break in between cat and dog and in between dog and
> sheep. Any ideas?

--

Dave Peterson

[Josh Craig]

Hi Dave,

I've tried that and the page breaks are fine except I don't want any
subtotals or totals calculated. Unless you know of a way to get the page
breaks without excel making any calculations??

"Dave Peterson" wrote:

> I'd use data|subtotals.
>
> There's an option to insert page breaks between groups on that dialog.
>
> And I could even get subtotals (counts, sums, averages,...) if I wanted them.
>
> Josh Craig wrote:
> >
> > Hi,
> >
> > I was wondering if anyone knew how to create a macro to insert a page break
> > after a number of values in a column. For example:
> >
> > Column A
> > Category
> > Cat
> > Cat
> > Cat
> > Dog
> > Dog
> > Sheep
> > Sheep
> > Sheep
> >
> > I want to insert a page break in between cat and dog and in between dog and
> > sheep. Any ideas?
>
> --
>
> Dave Peterson
>

[Steel Monkey]

Here is what i would do
Assuming the data in column a has been sorted i would write a macro with the following code:

Sub pagebreak()
Dim value1 As String
Dim value2 As String

Range("A2").Select
Do While ActiveCell.Value <> ""
value1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
value2 = ActiveCell.Value
If value1 <> value2 Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Else
End If
Loop
End Sub

[Gord Dibben]

Josh

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


Gord Dibben MS Excel MVP

On Wed, 5 Jul 2006 18:20:01 -0700, Josh Craig
<[email protected]> wrote:

>Hi Dave,
>
>I've tried that and the page breaks are fine except I don't want any
>subtotals or totals calculated. Unless you know of a way to get the page
>breaks without excel making any calculations??
>
>"Dave Peterson" wrote:
>
>> I'd use data|subtotals.
>>
>> There's an option to insert page breaks between groups on that dialog.
>>
>> And I could even get subtotals (counts, sums, averages,...) if I wanted them.
>>
>> Josh Craig wrote:
>> >
>> > Hi,
>> >
>> > I was wondering if anyone knew how to create a macro to insert a page break
>> > after a number of values in a column. For example:
>> >
>> > Column A
>> > Category
>> > Cat
>> > Cat
>> > Cat
>> > Dog
>> > Dog
>> > Sheep
>> > Sheep
>> > Sheep
>> >
>> > I want to insert a page break in between cat and dog and in between dog and
>> > sheep. Any ideas?
>>
>> --
>>
>> Dave Peterson
>>

[Josh Craig]

Works like a charm. Thanks heaps, Steel Monkey.

"Steel Monkey" wrote:

>
> Here is what i would do
> Assuming the data in column a has been sorted i would write a macro
> with the following code:
>
> Sub pagebreak()
> Dim value1 As String
> Dim value2 As String
>
> Range("A2").Select
> Do While ActiveCell.Value <> ""
> value1 = ActiveCell.Value
> ActiveCell.Offset(1, 0).Select
> value2 = ActiveCell.Value
> If value1 <> value2 Then
> ActiveCell.EntireRow.Select
> ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
> Else
> End If
> Loop
> End Sub
>
>
> --
> Steel Monkey
> ------------------------------------------------------------------------
> Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
> View this thread: http://www.excelforum.com/showthread...hreadid=558694
>
>