conditional page shift in Excel

Copper Contributor

I have an Excel sheet with the first column showing order numbers from a webshop. The second column shows product codes.

I want to print with page shift for each shift in order number in the first column.

I can, of course, insert a manual page shift, but I want an automatic page shift because of the numerous order numbers.


2 Replies


Run this macro:

Sub AddPageBreaks()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
        If Range("A" & r).Value <> Range("A" & r - 1).Value Then
            ActiveSheet.HPageBreaks.Add Before:=Range("A" & r)
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

@Hans Vogelaar 

Thank you very much!