Results 1 to 11 of 11

Thread: Page break (XP)

  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page break (XP)

    Hi,
    I have a sheet with many tables (results by country) which I want to print.
    By using the automatic page break tables may be split and shown on two pages, what I want to avoid.

    As a fixed point each table has in column A the string "Total" at the bottom.
    How can I tell the automatic page break to look upwards to the next "Total" and make a manual page break there.

    In addition (but unlikely) one table may be longer than one page. Then I want to start on top of a page and end obviously on the next page.

    TIA

    Wolf

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page break (XP)

    The following macro will clear existing manual horizontal page breaks, then add new ones under each "Total" in column A.

    Sub BreakOnTotal()
    Dim oCell As Range
    Dim strAddress As String
    Dim i As Integer

    ' Delete existing horizontal page breaks
    For i = ActiveSheet.HPageBreaks.Count To 1 Step -1
    ActiveSheet.HPageBreaks(i).Delete
    Next i

    ' Add new page breaks under the word Total
    With ActiveSheet.Range("A:A")
    Set oCell = .Find("Place", LookIn:=xlValues)
    If Not oCell Is Nothing Then
    strAddress = oCell.Address
    Do
    oCell.Offset(1, 0).EntireRow.PageBreak = xlPageBreakManual
    Set oCell = .FindNext(oCell)
    Loop While Not oCell Is Nothing And Not oCell.Address = strAddress
    End If
    End With
    End Sub

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page break (XP)

    Dear Hans,

    thanks for your answer.

    <<ActiveSheet.HPageBreaks(i).Delete>>
    gives an error for automatic page breaks, which I could trap with "on error...".

    <<then add new ones under each "Total" in column A.>>
    That works perfectly, but I would like to have as many full tables as possible on one page. This means that it should look for the next "Total" above an automatic page break and put a manual page break there.

    I tried several things already, but never succeded.

    Wolfgang

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page break (XP)

    Thank you very much Hans,
    it works perfectly.

    Just a tiny typo (if you want to use it again)
    <<ActiveSheet.HPageBreaks.Add Before:=_>>
    The space before the underscore is missing.

    Wolf

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page break (XP)

    Edited by HansV to correct typo

    Try this version:

    Sub BreakOnTotal2()
    Dim hpb As HPageBreak
    Dim lngPrev As Long
    Dim lngCur As Long
    Dim lngRow As Long
    ' Delete existing horizontal manual page breaks
    For Each hpb In ActiveSheet.HPageBreaks
    If hpb.Type = xlPageBreakManual Then
    hpb.Delete
    End If
    Next hpb
    ' Loop through page breaks and insert manual breaks as needed
    lngPrev = 1
    For Each hpb In ActiveSheet.HPageBreaks
    lngCur = hpb.Location.Row
    For lngRow = lngCur - 1 To lngPrev Step -1
    If ActiveSheet.Range("A" & lngRow) = "Total" Then
    If lngRow < lngCur - 1 Then
    ActiveSheet.HPageBreaks.Add Before:= _
    ActiveSheet.Range("A" & (lngRow + 1))
    End If
    Exit For
    End If
    Next lngRow
    Next hpb
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page break (XP)

    Thanks for pointing out the typo, I have corrected it.

    (That instruction was on one line in my test code, but it came out too wide in the post, so I modified it in the post instead of in the VB Editor <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>)

  7. #7
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page break (XP)

    Hi Hans,

    <<it works perfectly>>
    but only, when stepping manually through the macro.
    <F5> gave me wrong results.
    I was sure that it was a timing issue, therefore I tried e.g. wait and others, but in vaine.

    Hours later......

    I added after

    If lngRow < lngCur - 1 Then
    ActiveSheet.HPageBreaks.Add Before:= _
    ActiveSheet.Range("A" & (lngRow + 1))

    hpb.Location.Select

    Bingo. Not that it did anything, but it consumed the time needed to recalculate the automatic page breaks (I guess).

    After that I called this macro from the main macro (which creates the table). Again scrambled pagebreaks.
    Later I realized, that my main macro has screenupdating switched off. Setting it to true did the trick.

    I wonder if my theory of the "timing" is correct, but at the end it all works.

    BTW:
    I still got an error message (sometimes) in
    ....If hpb.Type = xlPageBreakManual Then....
    but I could successfully trap this with "on error."

    At least, VBA is not boaring.

    Wolf

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page break (XP)

    I think your theory is correct. When adding a manual page break, all automatic page breaks below it have to be recalculated. This is probably deferred if you turn off screen updating, and also if the page breaks are offscreen. By forcing Excel to move to the location of the page break, you force page breaks to be recalculated.

    Page breaks are tricky things to manipulate in code - if you use the macro recorder for adding or deleting page breaks, the generated code sometimes doesn't work at all. Glad you found solutions to the problems.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page break (XP)

    >Page breaks are tricky things to manipulate in code

    When working through this a while ago, I found that switching to PageBreakPreview in code made for consistent execution on all relevant machines. Something like:<pre> ActiveWindow.View = xlPageBreakPreview`
    'set PrintArea to a single page
    .VPageBreaks(1).DragOff xlToRight, 1
    Set .HPageBreaks(1).Location = Range("A70")
    Set .HPageBreaks(2).Location = Range("A95")
    ActiveWindow.View = xlNormalView</pre>

    Does this not work for you? <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> HTH
    Gre

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page break (XP)

    Thanks for the tip, sounds promising. I'll try it out next time I have occasion to use code involving page breaks.

  11. #11
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Page break (XP)

    I agree with Hans (as usual)! This is an excellent tip! I found that there were pagebreaks missing from the HPageBreaks collection no matter what I did. I tried forcing a print preview figuring that would force the collection to be properly populated, but I didn't like that because it meant the user had to close it (so I'm not sure if that solved it or not). I tried printing to a file, that didn't work. I selected the printarea range, then selected each cell in the range. Neither worked. I added the line of code to put the sheet in pagebreak view and it solved the whole problem. Of course, I had to switch back to normal view before copying the cells or else I got the "Page x" banner across it.
    I only wish I had looked here first!
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •