inserting page breaks automtically

[hywt]

Hi

I have a spreadsheet which I need to print out and distribute.

There are 5000 rows and the data starts in row 10. Rows 1 -9 contain some general information that needs to be included at the top of every printed page

Column A contains a location and there needs to be a page break at every change in data:

LOC 1
LOC 1
LOC 1
PAGE BREAK
LOC 2
LOC 2
LOC 2
PAGE BREAK
LOC 3
LOC 3


There are approximately 800 different locations.

Can anyone provide any pointers?

Thanks
Andy

[Tom Ogilvy]

Sub AB()
Dim rng As Range, cell As Range
Set rng = Range(Cells(10, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If cell.Value <> cell.Offset(-1, 0).Value _
And cell.Row <> 10 Then
ActiveSheet.HPageBreaks.Add Before:=cell
End If
Next
End Sub

--
Regards,
Tom Ogilvy





"hywt" <[email protected]> wrote in message
news:[email protected]...
>
> Hi
>
> I have a spreadsheet which I need to print out and distribute.
>
> There are 5000 rows and the data starts in row 10. Rows 1 -9 contain
> some general information that needs to be included at the top of every
> printed page
>
> Column A contains a location and there needs to be a page break at
> every change in data:
>
> LOC 1
> LOC 1
> LOC 1
> PAGE BREAK
> LOC 2
> LOC 2
> LOC 2
> PAGE BREAK
> LOC 3
> LOC 3
>
>
> There are approximately 800 different locations.
>
> Can anyone provide any pointers?
>
> Thanks
> Andy
>
>
> --
> hywt
> ------------------------------------------------------------------------
> hywt's Profile:
http://www.excelforum.com/member.php...o&userid=27656
> View this thread: http://www.excelforum.com/showthread...hreadid=471729
>

[hywt]

Many thanks Tom. Worked first time and saved me a few hours working out how to write it.

Andrew