I have an extensive construction completion list, that can be sorted by
room#, subcontractor, floor, author, date, etc. In each room there may be
multiple items that need to be fixed, each item is a separate record. When I
sort the list by room # I want to be able to automatically (or with as little
labor as possible) have page breaks inserted after each room, so that when I
print out the spreadsheet I can have a sheet for each room. Help please.

You can do that with Data=>Subtotals

You will have to generate a subtotal for the room, but that might be useful.

In the bottom of the dialog, select pagebreak between groups.

--
Regards,
Tom Ogilvy




"Winston" wrote:

> I have an extensive construction completion list, that can be sorted by
> room#, subcontractor, floor, author, date, etc. In each room there may be
> multiple items that need to be fixed, each item is a separate record. When I
> sort the list by room # I want to be able to automatically (or with as little
> labor as possible) have page breaks inserted after each room, so that when I
> print out the spreadsheet I can have a sheet for each room. Help please.

Change roomcol as required

Sub SetPasgeBredaks()
'
' Macro1 Macro
' Macro recorded 7/12/2008 by Joel
'

'
RoomCol = "A"
With ActiveSheet
.ResetAllPageBreaks
LastRow = .Range(RoomCol & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
If .Range(RoomCol & RowCount) <> _
.Range(RoomCol & (RowCount - 1)) Then

.HPageBreaks.Add Before:=.Range("A" & RowCount)
End If
Next RowCount
End With
End Sub


"Winston" wrote:

> I have an extensive construction completion list, that can be sorted by
> room#, subcontractor, floor, author, date, etc. In each room there may be
> multiple items that need to be fixed, each item is a separate record. When I
> sort the list by room # I want to be able to automatically (or with as little
> labor as possible) have page breaks inserted after each room, so that when I
> print out the spreadsheet I can have a sheet for each room. Help please.