Format Page breaks
 

Hi,

I'm trying to write a macro that will sort sheet by a particular column "B"
and then llok through that column and set a page break whenever the value of
any row in comumn "B" changes.

For example:
A B C
1 r t g
---------------------- Page Break
2 d u w
3 s u e
---------------------- Page Break
4 e v d

Any Ideas?

Thanks in advance

Format Page breaks
 

Give this macro a try (change the worksheet name reference to the worksheet
name you want to apply this code to)...

Sub AddPageBreaks()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
.Cells.PageBreak = xlPageBreakNone
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For X = 2 To LastRow
If .Cells(X - 1, "B").Value < .Cells(X, "B").Value Then
.Rows(X).PageBreak = xlPageBreakManual
End If
Next
.Rows(LastRow + 1).PageBreak = xlPageBreakManual
End With
End Sub

--
Rick (MVP - Excel)


"Matt G" wrote in message
...
Hi,

I'm trying to write a macro that will sort sheet by a particular column
"B"
and then llok through that column and set a page break whenever the value
of
any row in comumn "B" changes.

For example:
A B C
1 r t g
---------------------- Page Break
2 d u w
3 s u e
---------------------- Page Break
4 e v d

Any Ideas?

Thanks in advance

Format Page breaks
 

Try this one.

Assumes no header row.

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
Dim rng1 As Range
Set rng1 = Range(Cells(, 2), Cells(Rows.Count, Cells(, 2).Column).End(xlUp))
rng1.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
OldVal = Range("B1")
For Each rng In rng1
If rng.Text < OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.Text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Feb 2009 13:08:01 -0800, Matt G
wrote:

Hi,

I'm trying to write a macro that will sort sheet by a particular column "B"
and then llok through that column and set a page break whenever the value of
any row in comumn "B" changes.

For example:
A B C
1 r t g
---------------------- Page Break
2 d u w
3 s u e
---------------------- Page Break
4 e v d

Any Ideas?

Thanks in advance

Format Page breaks
 

That works great! I am trying to format the borbers to appear around each
cell but I can't get it to limit the formatting to cells with value. I don't
want to format more cells than I need to beacuse I'll end printing a bunch of
pages of empty boxes.

Can the "Dim" command be used for that?

"Rick Rothstein" wrote:

Give this macro a try (change the worksheet name reference to the worksheet
name you want to apply this code to)...

Sub AddPageBreaks()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
.Cells.PageBreak = xlPageBreakNone
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For X = 2 To LastRow
If .Cells(X - 1, "B").Value < .Cells(X, "B").Value Then
.Rows(X).PageBreak = xlPageBreakManual
End If
Next
.Rows(LastRow + 1).PageBreak = xlPageBreakManual
End With
End Sub

--
Rick (MVP - Excel)


"Matt G" wrote in message
...
Hi,

I'm trying to write a macro that will sort sheet by a particular column
"B"
and then llok through that column and set a page break whenever the value
of
any row in comumn "B" changes.

For example:
A B C
1 r t g
---------------------- Page Break
2 d u w
3 s u e
---------------------- Page Break
4 e v d

Any Ideas?

Thanks in advance