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?

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

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

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 Befo=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

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

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 Befo=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

Looking at this I think it is in the direction of my problem. What I am
tryiong to do is set page break based on the size of cell in column (M). This
cell varies in size and I am trying to have its contents not split to second
page. I have never done page breaks before. If my idea is correct the
selection row could be changed to cell size? Cell is changed to wrap when
printing. All assistance greatly appreciated.
Thanks You

"Gord Dibben" wrote:

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