Page Breaks

[ronicaroman]

I'm trying to creat a macro that will add page breaks for each change in column A, so there should be a page break between RA and DZ in the example below. Is there a simple formula and/or VBA method for doing this?

ColumA...Amount
RA.........25
RA.........25
RA.........75
DZ.........300
DZ.........300
DZ.........55

 

[xlHammer]

You could loop through the contents of column A and add a page break when two consecutive cells differ in value, for example;

Sub addpagebreaks()
    Dim a1 As Variant, a2 As Variant
    Dim b1 As Integer
    b1 = 1
 
    Do
        a1 = Range("A1").Offset(b1, 0).Value
        a2 = Range("A1").Offset(b1 - 1, 0).Value
        If a1 <> a2 Then
            Worksheets("Sheet1").Rows(b1 + 1).PageBreak = xlPageBreakManual
        End If
        b1 = b1 + 1
    Loop Until a1 = ""
End Sub

Nb. Use xlPageBreakNone if you want to remove the page breaks again.

 

[ronicaroman]

How would the above differ if I actually have 20 columns (A-T), and I need the page breaks added frome every change in column G?

 

[ronicaroman]

Please ignore the questions above. Thanks for the code, I was able to tweek it for my 20 column worksheet!

But now I'm having a problem with the first page. Since the column title is different then the value in A2 the code creates a seperate page for row 1. How can I fix this problem. I tried playing with the code a bit to fix this issue but wasn't able to do it. I know I'm missing something obvious.

 

[xlHammer]

Change the starting value of b1

Sub addpagebreaks()
 
    Dim a1 As Variant, a2 As Variant
    Dim b1 As Integer
    [COLOR=red]b1 = 2[/COLOR]
 
    Do
        a1 = Range("A1").Offset(b1, 0).Value
        a2 = Range("A1").Offset(b1 - 1, 0).Value
        If a1 <> a2 Then
            Worksheets("Sheet1").Rows(b1 + 1).PageBreak = xlPageBreakManual
        End If
        b1 = b1 + 1
    Loop Until a1 = ""
 
End Sub