How do I insert a page break before each change in the cell conten

[Guest]

I need to write a Macro or VB Script that will insert page breaks above each
row that contains new contents in a particular cell. One column contains the
work location for our employees in code form (i.e. N002), and page breaks
need to be inserted to separate each work location, so there would need to be
a page break at the point that the Work location code changes from N002 to
N003. This is an extremely time-consuming task that has to be performed on
multiple reports every two weeks. Your help is appreciated!!

 

[Gord Dibben]

ronda

To insert the pagebreaks.

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

Note gocush's comments and questions also.

Gord Dibben Excel MVP

 

[Guest]

OK,
1. It is a list of existing employees who periodically change work
locations, however (this answers #2, also) I am downloading the list out of a
network data program, so I am not recreating the list each time.

3. Yes, it is sorted.

One last thing, I am super-newbie at VB script. Any suggestions for
learning the language would be awesome.

Thanks!

 

[Guest]

Yah, let me reply again, because I'm stupid! In the place of the "i" should
I be giving the column letter, or a range of cells? I'm assuming!

 

[Gord Dibben]

ronda

Assuming the work location column has no blanks, just select top cell of that
column then SHIFT + END + DownArrow then run the macro through
Tools>Macro>Macros.

Gord

 

[Dave Peterson]

Another option would be to use Data|Subtotals.

There's an option to insert a "page break between groups".

And you'd get the subtotals, too!