Page Break when a column changes

[jaimeA]

Hi, I have a huge table which I have to update every week and the
insert page breaks after a cahnge on column A

the data looks like this:

Author (Title is always at A6) .....
Peter
Total 1
Total 2
Jason
Total 1
Total 2

Is it possible to create a macro that will insert pagebreaks after an
change in column A, after row A6 (so it will exclude the title and inf
that comes before that), but that it also excludes changes that includ
the fixed text "total 1" and "total 2"?

So it will look:

Author
Peter
Total 1
Total 2

(page break)

Jason
total 1
Total 2

Thanks for your help!

 

[Claus Busch]

Hi,

Am Mon, 18 Nov 2013 17:59:19 +0000 schrieb jaimeA:

Is it possible to create a macro that will insert pagebreaks after any
change in column A, after row A6 (so it will exclude the title and info
that comes before that), but that it also excludes changes that include
the fixed text "total 1" and "total 2"?

So it will look:

Author
Peter
Total 1
Total 2

(page break)

Jason
total 1
Total 2

try:
Sub PageBreak()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.ResetAllPageBreaks
For Each rngC In Range("A6:A" & LRow)
If rngC = "Total 2" Then
.HPageBreaks.Add rngC.Offset(1, 0)
End If
Next
End With
End Sub


Regards
Claus B.

 

[jaimeA]

Hi,

Am Mon, 18 Nov 2013 17:59:19 +0000 schrieb jaimeA:
-

try:
Sub PageBreak()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.ResetAllPageBreaks
For Each rngC In Range("A6:A" & LRow)
If rngC = "Total 2" Then
.HPageBreaks.Add rngC.Offset(1, 0)
End If
Next
End With
End Sub


Regards
Claus B.

Thank you, this works on 80% of the cases, but realize something else
not all the Authors will have a Total 2, some of them only have a Tota
1. Any idea on how I can work around this? Thank

 

[Claus Busch]

Hi Jaime,

Am Mon, 18 Nov 2013 20:24:28 +0000 schrieb jaimeA:

Thank you, this works on 80% of the cases, but realize something else,
not all the Authors will have a Total 2, some of them only have a Total
1. Any idea on how I can work around this?

then try:

Sub PageBreak()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.ResetAllPageBreaks
For Each rngC In Range("A6:A" & LRow + 1)
If Left(rngC, 1) <> "T" Then
.HPageBreaks.Add rngC
End If
Next
End With
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Mon, 18 Nov 2013 21:55:42 +0100 schrieb Claus Busch:

Sub PageBreak()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.ResetAllPageBreaks
For Each rngC In Range("A6:A" & LRow + 1)
If Left(rngC, 1) <> "T" Then
.HPageBreaks.Add rngC
End If
Next
End With
End Sub

better try:

Sub PageBreak()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.ResetAllPageBreaks
For Each rngC In Range("A6:A" & LRow + 1)
If Left(rngC, 5) <> "Total" Then
.HPageBreaks.Add rngC
End If
Next
End With
End Sub


Regards
Claus B.