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!

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



cbuck wrote:

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

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 Befo=Cells(dX, 1)
'reset the value to compare
s1 = s2
End If
Next
End Sub


Hope that helps!
Cory

On Oct 31, 11:57 am, cbuck wrote:
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!

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

On Wed, 31 Oct 2007 09:57:01 -0700, cbuck
wrote:

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!

Thank you, Dave. That was very helpful!

"Dave Peterson" wrote:

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



cbuck wrote:

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