Conditional Page Breaks

I have a data base with department's name ( in column A ) and staff names ,local number and extension number and other information in other columns, this report can have up to 15000 lines

When I print it , I would like a page break to be inserted only before a new department .

I do not want to have one department per page , having 3 departments on one page is not an issue , but I would like the page break to
(as much as possible) always be inserted before a new department .
And for departments who will need more than one page , a page break should be inserted before it meets an content in column C

If anyone can help , that would be great !

Thanks in advance

How would I have a page break created automatically, if possible, when the number 13 appears in column K of a worksheet? That is, I want a page break after the row with a value of 13 in row k, or before the row with a 14, whichever is easier. I'd prefer not to have to run a macro each time, as I have a lot of worksheets to print. Basically, I want everything to fit on one page per worksheet, just so I don't wind up with one or two rows on a page 2, but I don't want it to squeeze everything onto one sheet if it's going to make the font too small. Is there an easy way to do this that doesn't require running a macro for each worksheet? If not, what would the VBA be to run a macro for each worksheet, or even one macro for all worksheets in a range?

Thank you!

Gary

I need to insert page break before the word Carton No on the excel spreadsheet as below. Is there any function that I can use so I can automatically add page break before certain word?

Carton No : 00093453420068573061
Weight : 2.00

Style
ZR1601013F
ZR1601013F
ZR1601013F
ZR1601013F



Carton No : 00093453420068573207
Weight : 2.00

Style
ZR1601202F
ZR1601202F
ZR1601202F
ZR1601202F

@Deanna : probably you could use Pivot Table first before Subtotal feature.

This has the potential to really help me with a project I'm working on. Instead of inserting a page break each time the contents of a column change, I need to insert a new page break before each occupied cell (or after the last blank cell).

For example, A1 is Person 1, A2 is blank, A3 is blank, A4 is Person 2, A5 is blank, A6 is Person 3, etc. with varying numbers of blanks between persons.

I would like to be able to print the data associated with each person individually.

Please let me know if you have any solutions or if you need more information. Thank you!

I have done some digging and discovered that HPageBreak, HPageBreaks, VPageBreak, VPageBreaks can be used to manage the page breaks in VBA. The suggested PageBreaks property of the Range object cannot be used anymore for VBA controlled page break management. At least not for MS Office 2010. Here is the modified code that does the job:

Sub PageBreak101()
Dim CellRange As Range
Dim TestCell As Range

nCells = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count)
Set CellRange = Range(ActiveSheet.UsedRange.Cells(2), ActiveSheet.UsedRange.Cells(nCells))
For Each TestCell In CellRange
If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
ActiveSheet.HPageBreaks.Add ActiveSheet.Rows(TestCell.Row)
End If
Next TestCell
End Sub

I receive the same error even at the first attempt to set the PageBreak property of the Range object. Has anything changed in MSOffice? I am using Excel 2010.

I don't see why in the macro the variable CellRange is declared, then set to Selection and finaly use it only once. These 3 lines:

Dim CellRange As Range
Set CellRange = Selection
For Each TestCell In CellRange

Can be replaced by this 1 line:

For Each TestCell In Selection

It's perhaps no big deal but it serves simplicity and readability.

Very VERY nice tip. I would suggest to leave the "Summary below data" box unchecked as, if checked, this creates an unnecessary line which shows that count is zero. Since we're really taking advantage of a ""side effect": of the subtotals routine, we don't need this summary line.

I have found a Microsoft kb article which says Excel has limited the manual page break horizonally to 1026 ??? :(

http://support.microsoft.com/kb/284916

Regards,
Marakkar

Excel 2010
Windows 2007

I have been using your VBA code in excel macro for sometime without any issue. All my use was to insert less than a 1000 of pagebreaks in a sheet.

Now I have worksheet of inventory of 8163 records that is for materials lised in 1132 boxes which need page breaks, i.e. each box will have less than 10 lines.

I have sorted the liste on box and selected the column applied the macro. Page break was inserted for 1023 changes of boxes and stopped to popup a Microsoft Visual Basic error message:
Runtime error '1004':
Unable to set the PageBreak property of Range class
with End / Debug button.

I even tried to select the remaining lines in the series still the same error appears. I am unable to debug and find the limitation in the code.

Kind help me to use this very valuble macro code.

Regards
Marakkar