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]

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

 

[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

 

[Josh Craig]

Works like a charm. Thanks heaps, Steel Monkey.

 

[Curt]

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