page break after
 

lets say i have sally typed in rows 1-5, bob in rows 6-10, and john in rows
11-15. how can i work it so excel automatically inserts a page break after
each name in the cell changes. in other words a page break between sally and
bob, bob and john.

this spreadsheet is massive!

please help!

page break after
 

Hi

Sub test()
Dim c As Range, r As Range
With ActiveSheet
..ResetAllPageBreaks
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
If c < c.Offset(-1, 0) Then .HPageBreaks.Add Befo=c
Next c
End With
End Sub

--

XL2003
Regards

William



"eager beaver" <eager wrote in message
...
lets say i have sally typed in rows 1-5, bob in rows 6-10, and john in
rows
11-15. how can i work it so excel automatically inserts a page break after
each name in the cell changes. in other words a page break between sally
and
bob, bob and john.

this spreadsheet is massive!

please help!

page break after
 

Hi,

Making a few assumptions here from missing data not provided. Assuming that
you have headers in row 1 and your data starts on row 2, and that the names
are listed in coumn C of who put the information in, you could use something
like this ...

Sub Name_PageBreak1()
Dim i As Long, lngLastRow As Long
lngLastRow = Range("A65536").End(xlUp).Row
For i = 2 To lngLastRow Step 1
If Cells(i, 3) < Cells(i - 1, 3) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add befo=Cells(i, 3)
End If
Next
MsgBox "Complete!"
End Sub

If you don't have the names listed and you are assuming that every 5 rows a
different user will have entered data, you could use something like this ...

Sub Name_PageBreak2()
Dim i As Long, lngLastRow As Long
lngLastRow = Range("A65536").End(xlUp).Row
For i = 5 To lngLastRow Step 5
ActiveWindow.SelectedSheets.HPageBreaks.Add befo=Rows(i + 1)
Next
MsgBox "Complete!"
End Sub

Note this second procedure assumes that there is NO HEADERS. HTH

--
Regards,
Zack Barresse, aka firefytr


"eager beaver" <eager wrote in message
...
lets say i have sally typed in rows 1-5, bob in rows 6-10, and john in
rows
11-15. how can i work it so excel automatically inserts a page break after
each name in the cell changes. in other words a page break between sally
and
bob, bob and john.

this spreadsheet is massive!

please help!

page break after
 

Hi William,

I'm put in what you gave me ... its possible i didnt put it in the right
place ( VB)... but it didnt work.. im sorry im not a programmer.....


"William" wrote:

Hi

Sub test()
Dim c As Range, r As Range
With ActiveSheet
..ResetAllPageBreaks
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
If c < c.Offset(-1, 0) Then .HPageBreaks.Add Befo=c
Next c
End With
End Sub

--

XL2003
Regards

William



"eager beaver" <eager wrote in message
...
lets say i have sally typed in rows 1-5, bob in rows 6-10, and john in
rows
11-15. how can i work it so excel automatically inserts a page break after
each name in the cell changes. in other words a page break between sally
and
bob, bob and john.

this spreadsheet is massive!

please help!

page break after
 

To install:

From Excel, press Alt + F11 to open the Visual Basic Editor (VBE)
Press Ctrl + R, to open Project Explorer
Select your file in bold on left pane
Select Insert (menu) | Module
Paste code on right (code) pane
Alt + Q will return to Excel
SAVE BEFORE RUNNING ANY CODE!

HTH

--
Regards,
Zack Barresse, aka firefytr


"eager beaver" <eager wrote in message
...
Hi William,

I'm put in what you gave me ... its possible i didnt put it in the right
place ( VB)... but it didnt work.. im sorry im not a programmer.....


"William" wrote:

Hi

Sub test()
Dim c As Range, r As Range
With ActiveSheet
..ResetAllPageBreaks
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
If c < c.Offset(-1, 0) Then .HPageBreaks.Add Befo=c
Next c
End With
End Sub

--

XL2003
Regards

William



"eager beaver" <eager wrote in message
...
lets say i have sally typed in rows 1-5, bob in rows 6-10, and john in
rows
11-15. how can i work it so excel automatically inserts a page break
after
each name in the cell changes. in other words a page break between
sally
and
bob, bob and john.

this spreadsheet is massive!

please help!