Page break problem!

[mrowe]

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

 

[Dave Hawley]

Hi Matt

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

If this is no good you will need VBA using the pagebreak Object.

 

[mrowe]

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

 

[mrowe]

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

 

[Dave Hawley]

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

 

[mrowe]

thanks - can you give me some comments on how this one works, just so I know for next time... thanks

 

[Dave Hawley]

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.