See one answer in microsoft.public.excel

Message-ID:


In article ,
"xL User" wrote:

Yes, I misspelled keywords in previous message. Here is a real example from
which I started to use HPageBreaks.

Sub Macros1()

Range("A1:A59").Select
ActiveWindow.SelectedSheets.PrintPreview
Set ActiveSheet.HPageBreaks(1).Location = Range("A31")
Range("A44").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("A39").Select
Set ActiveSheet.HPageBreaks(1).Location = Range("A30")
ActiveWindow.SelectedSheets.PrintPreview
End Sub

This macro was recorded in XL of OfficeXP (Rus) while I set page breaks
manually. It is very ridiculous result of macro recording because when I
launched the macro XL gave me the same run-time error 1004 at the string "Set
ActiveSheet..." :-)
Here is an example of my real code:

ActiveSheet.PageSetup.PrintArea = "$A$1:$A$" + Format$(last_row, "0")
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.55)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 3
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveSheet.ResetAllPageBreaks
ActiveSheet.HPageBreaks.Add befo=ActiveSheet.Cells(jur_row, 1)
ActiveSheet.HPageBreaks.Add befo=ActiveSheet.Cells(bez_row, 1)
ActiveWindow.SelectedSheets.PrintPreview

And no use :-(
In print preview, I can see no page breaks. As you can see, I have set
.PrintArea and page breaks falls into .PrintArea range. I need 3 pages to
print divided an certain rows, but I can't achieve desired result. More
simply would be using separate worksheets for separate printout pages, but
the task is to place all information in one worksheet and make it print on 3
pages :-(

I've honestly tried to use string
Application.ExecuteExcel4Macro "SET.PAGE.BREAK()"
and have no success. In print preview page breaks are set in other locations
of that I have tried to set...

Any other suggestions?
Maybe I should place future pages in different columns and use VPageBreaks?