Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
change and/or remove page number watermark in page break preview | Excel Discussion (Misc queries) | |||
Hiding the page number in page break preview background. | Excel Discussion (Misc queries) | |||
adding a new page break to an existing page break | Excel Discussion (Misc queries) |