Hi, I have a huge table which I have to update every week and then 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 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

Thanks for your help!!

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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? Thanks

I found something that works:

Sub Macro1()

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 Like "*(?????)" Then
.HPageBreaks.Add rngC.Offset(1, 10)
End If
Next
End With


End Sub


I found that the common denominator here was the ID between parenthesis. So this works, I just need it to insert the page break before that line and not after like it is doing it right now. Any ideas?? Thanks for the code!

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2