Multiple Page Breaks

[Susan]

Is there a way to insert multiple page breaks at highlighted cells?
Let's say I do a Go To->Special->Constants to select certain cells in a
column where I want to insert a page break. Is this possible?

Thank you.

 

[Gord Dibben]

You cannot insert pagebreaks at multiple selected cells without using VBA code
to find and insert the breaks.


This macro would insert a pagebreak at each change of data in a selected range
like A1:A7

qwerty
qwerty
qwerty
pagebreak
asdfgh
asdfgh
pagebreak
zxcvbn
zxcvbn

Sub InsertBreak_At_Change()
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub

With more detail we could probably tailor one to suit your needs.


Gord Dibben MS Excel MVP

 

[Susan]

Thanks,

I tried the code you had included on a test sample just to see how it works,
but it gave me an error (400).

Let me see if I can describe what I'm doing.
I have a spreadsheet given to me with subtotals in it. I want a page break
at each subtotal. There are a lot of formulas just linking here and there,
which is why I don't want to do a remove all and redo the subtotals with a
Page Break separating the sections. So I was thinking that I'd just mark
each subtotal row with say, an "X" or something, and insert a page break at
those points.

Here's a nutshell glimpse of what it might look like:
Policy Premium Co#
Pol_1 $1000 001
Pol_2 $500 001
Pol_3 $1000 001
$2500 001 Total
Pol_4 $300 002
Pol_5 $1000 002
$1300 002 Total

So what I would want is a page break separating 001 from 002, without having
to remove all existing subtotal and redoing it.

Thanks,
Susan

 

[Susan]

Nevermind, I was missing one piece of punctuation in my code.
-___-

Thanks a bunch!

 

[Gord Dibben]

Don't know why you got the error(400).

Try this macro to look for "Total" in column 4(D)and insert a break below it.

Sub Insert_PBreak()
Dim Rng As Range
Dim cell As Range
Set Rng = Intersect(ActiveSheet.UsedRange, Columns(4))
For Each cell In Rng
If cell.Text = "Total" Then
cell.Offset(1, 1).PageBreak = xlPageBreakManual
'edit the (1, 1) to (1, x) if you want more columns to vertical break
End If
Next
End Sub


Gord