Force Page Breaks In Vba?

  • Hi, want to specify page breaks and set print areas when running a macro. Appears to work sometimes, but then breaks (hard and soft) pop up on their own).

    Currently using something like this to specify a 2 page wide view...

    ActiveSheet.PageSetup.PrintArea = "$A:$Z"
        With ActiveSheet.PageSetup
            .FitToPagesWide = 2
            Set ActiveSheet.VPageBreaks(1).Location = Range("P1")
        End With

    Can I force breaks to be more absolute (more accurately)?

  • Re: Force Page Breaks In Vba?

    For some reason in excel the FitToPageWide and tall doesn't work when put into a macro so there is not point in setting FitToPageWide = 2

    try doing something along the lines of this

    With ActiveSheet.PageSetup
            .CenterHorizontally = True
            .Orientation = xlLandscape
        End With
        ActiveWindow.View = xlPageBreakPreview
        ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        ActiveWindow.View = xlNormalView
  • Re: Force Page Breaks In Vba?

    Excellent, thanks for the pointer.

    Basically, I have 2 custom views (columns hidden in one, and not in the other).

    I want to be able to specify that the view with colums hidden (activated by clicking a command button) be set to one page wide only, and that the view containing all columns, be set to 2 pages wide (but no more).

    I want to be able to set the print areas to match also.

    Your suggestions are most welcome!

  • Re: Force Page Breaks In Vba?

    Ok try this

    should help you get there

  • Re: Force Page Breaks In Vba?

    Hmmm, Excel still seems to be inserting Vertical Page Breaks in wherever it sees fit, in addition to those I've specified.

    Is there a foolproof (i.e. me!) way of specifying where a vertical page break should be, without it adding in its own too?

  • Re: Force Page Breaks In Vba?

    Unfortunately, no.

    The data's being pulled in from Access, and there's a whole lot of info I'd need to censor. Would take an age! :)

  • Re: Force Page Breaks In Vba?

    How are you 'Pulling' your data from Access. If you query the database via a SQL Statement you could set two seperate queries one to pull through all your desired columns and the other to import required fields only... it would look something like this

    that could be a better way of doing it?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!