Multiple Page Breaks
 

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.

Multiple Page Breaks
 

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



On Thu, 28 Jun 2007 13:52:04 -0700, Susan
wrote:

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.

Multiple Page Breaks
 

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

"Gord Dibben" wrote:

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



On Thu, 28 Jun 2007 13:52:04 -0700, Susan
wrote:

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.

Multiple Page Breaks
 

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

Thanks a bunch!

"Susan" wrote:

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

"Gord Dibben" wrote:

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



On Thu, 28 Jun 2007 13:52:04 -0700, Susan
wrote:

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.

Multiple Page Breaks
 

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

On Fri, 29 Jun 2007 11:16:01 -0700, Susan
wrote:

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

"Gord Dibben" wrote:

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



On Thu, 28 Jun 2007 13:52:04 -0700, Susan
wrote:

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.