Control Pagebreaks Programatically

L

Laurence Lombard

I want to print a sheet columns A:N (rows vary) 1 page wide with pagebreaks
at certain rows (91+n*90) eg 91,181,271 etc but am having trouble with old
manual pagebreaks and the automatic pagebreaks.

The code below (modified from earlier advice on this newsgroup) results in
pagebreaks above 90,91,178,181,268 and 271 with BottomRow = 328.

How do control PageSetup to be one page wide with breaks at 91,181,271?
Thanks
Laurence
*******************************************************
Sub SetPageBreaks()
Dim hpb As HPageBreak
Sheets("Stuk P").Activate
BottomRow = Range("A2000").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = Range("A1", "N" & BottomRow).Address

ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = False

For Each hpb In ActiveSheet.HPageBreaks
If hpb.Type = xlPageBreakManual Then hpb.Delete
Next hpb

Breakline = 91
Do While Breakline < BottomRow
ActiveSheet.HPageBreaks.Add ActiveSheet.Range("A" & Breakline)
Breakline = Breakline + 90
Loop
End Sub
 
D

Dave Peterson

I don't think it's your code.

I think you're getting automatic pagebreaks intermingled with the manual
pagebreaks.

If you just do File|Page Setup|Page tab
and change the zoom to something smaller (25%-50%???), then do a print preview,
does it look ok?

ps:

ActiveSheet.ResetAllPageBreaks
will allow you to get rid of that "For each hpb" loop.
 
L

Laurence Lombard

Thanks for your reply, Dave

I realise that the manual and automatic Pagebreaks are getting mixed up. I
need to understand how Excel handles them. I'm going to search the web
before I ask further questions.

Thanks Laurence
 
D

Dave Peterson

Try to do some manual testing. If you run your code and make the zoom (on the
file|page setup) too large, you'll see that you'll get both manual and automatic
page breaks.

Then make that zoom factor really small. You'll see that you'll have to adjust
the zoom to eliminate the automatic page breaks.
 
Top