inserting page breaks for specific data groups

[Guest]

I have a list of data with three columns--I want to automatically insert page
breaks at the end of each group in a cloumn (for example: at change in
curriculum code insert page break). Is this possible? If so , how?

Ursula

 

[Gord Dibben]

Ursula

Insert a pagebreak at each change in column A

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 Fri, 29 Sep 2006 12:44:03 -0700, Ursula Forte <Ursula

 

[Guest]

Let me preface with "I'm new at using Macros". What if I want to insert a
pagebreak at each change in column B?

 

[Dave Peterson]

Gord's routine works off what you have selected.

But it also inserts a page break in column B.

I'm guessing that you want the page break for the whole row:

Option Explicit

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).EntireRow.Cells(1)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub

 

[Gord Dibben]

Sub Insert_Pbreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("B1")
Range("B1").Select
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select
For Each rng In Selection
If rng.Text <> OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.Text
End If
Next rng
End Sub


Gord