vba page-setup

[VegasNath]

What would be the best vba approach to setting the page setup as follows.

When the print button is activated, I want to print to 4 pages landscape (1cm margins).

Range A1:H91 - Pages 1 and 2
Range A93:H155 - Page 3
Range A157:H218 - Page 4.

Apologies if this seems a dull question, but I'm not even sure how to do this manually, and if I did, I know that the recorder spits out drivell in these situations.

Thanks for any help.

[HansV]

Try code like this (you'll have to modify it to suit your needs):

ActiveSheet.HPageBreaks.Add Before:=Range("A93")
Set ActiveSheet.HPageBreaks(3).Location = Range("A157")

[VegasNath]

Hans, I get a 'run time error 9' on the second line. When previewing the print, the page break does not seem to do anything.

I don't know how I am to use this code, would you mind elaborating slightly. Thanks

[HansV]

Why do you want to do this in code? Wouldn't it be easier to set the page breaks in page break view?

[VegasNath]

This is one of several daily text files that I receive that need to be converted to excel, reformatted etc.... and printed. I am trying to complete the entire task programatically.

[HansV]

I'd switch to page break view, record a macro of setting the page breaks correctly, and when you've stopped recording, edit out the superfluous code.

[VegasNath]

I was not familiar with working in page break view, but I am now.

Got there eventually...

Code: Select all

    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.PageSetup.PrintArea = "$A$1:$H$218"
    Set ActiveSheet.HPageBreaks(1).Location = Range("A69")
    Set ActiveSheet.HPageBreaks(2).Location = Range("A93")
    Set ActiveSheet.HPageBreaks(3).Location = Range("A157")

Thanks.

[HansV]

You could reset the original view, and minimize screen updating:

Code: Select all

    Dim lngView As Long
    Application.ScreenUpdating = False
    ' Store the current view
    lngView = ActiveWindow.View
    ' Switch to page break view
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.PageSetup.PrintArea = "$A$1:$H$218"
    Set ActiveSheet.HPageBreaks(1).Location = Range("A69")
    Set ActiveSheet.HPageBreaks(2).Location = Range("A93")
    Set ActiveSheet.HPageBreaks(3).Location = Range("A157")
    ' Restore the original view
    ActiveWindow.View = lngView
    Application.ScreenUpdating = True

[VegasNath]

Thanks Hans, good idea.