View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
BEEJAY BEEJAY is offline
external usenet poster
 
Posts: 247
Default Page Break Automation

I tried Gord D.'s "clunker".
Couldn't get it to work, even with help from the group.
Then I found the following, apparently originally from Frank Kabel.
Works great, EXCEPT, it also puts a page break under the header.
Can someone tell me how to get the following to ignore header rows.
If I can specify (within the module), the number of header rows,
this macro would be very versatile. (for many people)
Just specify how many header rows there are,
and which column is to be searched......
and Bob's your uncle.

Sub AAAInsertBreak()
' AAAInsertBreak Macro
' Insert Page Break after each change of
' Data in Column B
' From Frank Kabel, Germany

' I added the following reset
ActiveSheet.ResetAllPageBreaks

Dim lastrow As Long
Dim row_index As Long

'All the "B"'s were "A"'s, originally

lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value < _
Cells(row_index + 1, "B").Value Then
ActiveSheet.HPageBreaks.Add Befo= _
Cells(row_index + 1, "B")
End If
Next

'I added the Message Box
MsgBox "COMPLETE!"

End Sub