insert page break at each change in column B

[cbuck]

It sounds simple enough, but I haven't been able to find any information.

I need to insert a page break at each change of value in column B. For
example:

Column A = Name
Column B = Department

I need to insert a page break every time the department changes.

Any help would be greatly appreciated!

 

[Dave Peterson]

Maybe you could use data|subtotals (xl2003 menu system) to get subtotals -- make
sure you select page break between groups.

 

[crferguson]

Based on your column example and assuming you don't have a header row,
this code worked for me:

Option Explicit

Sub InsertPageBreaks()
Dim dX As Double, dRows As Double
Dim s1 As String, s2 As String

'get count of used rows
dRows = ActiveSheet.UsedRange.Rows.Count
'get initial value to start comparing with
s1 = Range("B1").Value

'loop through the rest of the values in column B
'starting in cell 2 (because you already know cell 1)
For dX = 2 To dRows
'get the next value
s2 = Cells(dX, 2).Value
'compare it
If s1 <> s2 Then
'if the values are different then add the break
ActiveSheet.HPageBreaks.Add Before:=Cells(dX, 1)
'reset the value to compare
s1 = s2
End If
Next
End Sub


Hope that helps!
Cory

 

[Gord Dibben]

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(2).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

 

[cbuck]

Thank you, Dave. That was very helpful!

Maybe you could use data|subtotals (xl2003 menu system) to get subtotals -- make
sure you select page break between groups.