Page break problem!

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
Hi,

I need to insert a page break when the value in colum F changes. It's a huge list and I'm sure there is an easier way!

Thanks in advance
Matt
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Matt

Have a look at the Subtotals.. feature under Data.

If this is no good you will need VBA using the pagebreak Object.
 
Upvote 0
Thanks.... I've had a look at that, but I don't know how to force the page break at "each change in". I also thought of doing it as a pivot table then splitting out the report page by page - but there are just to many changes.... can you give me a hit with the page break object?
Matt
 
Upvote 0
Sorry....

Can someone expand on this for me - I think it needs somesort of offset statement but I don't know how to do it.

Sub PageBreakAtChange()
Columns("E:E").Select
Selection.ColumnDifferences(ActiveCell).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End Sub
 
Upvote 0
Hi Matt


This is off the cuff, but should help


Sub SumCols()
Dim rCells As Range
ActiveSheet.DisplayAutomaticPageBreaks = False
For Each rCells In Range("F2", Range("F65536").End(xlUp))
If rCells <> rCells.Offset(-1, 0) Then
rCells.EntireRow.PageBreak = xlPageBreakManual
End If
Next rCells
End Sub
 
Upvote 0
thanks - can you give me some comments on how this one works, just so I know for next time... thanks
 
Upvote 0
Sub SumCols()
Dim rCells As Range
'Clear all current page breaks
ActiveSheet.DisplayAutomaticPageBreaks = False
'Loop through all cells in the range("F2:F<x>)
For Each rCells In Range("F2", Range("F65536").End(xlUp))

If rCells <> rCells.Offset(-1, 0) Then 'Change in value
'Add page break
rCells.EntireRow.PageBreak = xlPageBreakManual
End If
Next rCells
End Sub


To use it Push Alt+F11, go to Insert>Module and paste it in. Push Alt+Q then Alt+F8, then select the macro name and Click "Run". Just make sure you are on the correct sheet.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top