print a new page for each group

[Ben]

is it possible to have a page break automatically inserted when there is a
change in value in a particular column?

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>
--Assume you have the data in ColA starting from row2. and Row1
with headers...The macro will insert pagebreaks after each group of data in
Col A. Try print preview once you run the macro and feedback

Invoice Num | Part no. | Qty.
H0907-001 ERW123 20,000
H0907-001 EAW122 10,000
-----------------------------------
H0907-002 AWE112 50,000
H0907-003 BPR225 10,000
-----------------------------------
H0907-003 CRE123 5,000
-----------------------------------

Sub Pagebreaks()
Dim lngRow As Long
For lngRow = 3 To Cells(Rows.Count, "A").End(xlUp).Row + 1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
ActiveSheet.HPageBreaks.Add Before:=Range("A" & lngRow)
End If
Next
End Sub

If this post helps click Yes

 

[Jacob Skaria]

Forgot to mention that if the macro is run on a sample data as below. each
invoice group will be separated by a page-break

Invoice Num | Part no. | Qty.
H0907-001 ERW123 20,000
H0907-001 EAW122 10,000
H0907-002 AWE112 50,000
H0907-003 BPR225 10,000
H0907-003 CRE123 5,000

 

[Rick Rothstein]

Give this macro a try...

Sub InsertPageBreaks()
Dim X As Long, LastRow As Long
Const ColumnToMonitor As String = "E"
Const StartRow As Long = 2 'Assumes Row 1 is a header row
With ActiveSheet
LastRow = .Cells(.Rows.Count, ColumnToMonitor).End(xlUp).Row
For X = StartRow + 1 To LastRow
If .Cells(X, ColumnToMonitor).Value <> _
.Cells(X - 1, ColumnToMonitor).Value Then
.Rows(X).PageBreak = xlPageBreakManual
End If
Next
End With
End Sub

Set the ColumnToMonitor (which column has the values you are checking) and
the StartRow for your actual setup.

 

[Rick Rothstein]

Actually, use this macro instead... it allows you to change the break point
between existing sections and moves the breakpoints accordingly (actually,
it doesn't move them, it deletes all existing ones and then calculates the
position for the column as it currently exists).

Sub InsertPageBreaks()
Dim X As Long, LastRow As Long
Const ColumnToMonitor As String = "E"
Const StartRow As Long = 2 'Assumes Row 1 is a header row
With ActiveSheet
LastRow = .Cells(.Rows.Count, ColumnToMonitor).End(xlUp).Row
.Rows.PageBreak = xlNone
For X = StartRow + 1 To LastRow
If .Cells(X, ColumnToMonitor).Value <> _
.Cells(X - 1, ColumnToMonitor).Value Then
.Rows(X).PageBreak = xlPageBreakManual
End If
Next
End With
End Sub

 

[Ashish Mathur]

Hi,

Fist sort in alphabetical order the column containing the value based on
which you want to create a break. Then go to Data > Subtotal and check the
box for Page break between groups.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com