Formating Print Margins

  • Thread starter Thread starter glimmer23
  • Start date Start date
G

glimmer23

Ok I know this code is wrong and correct to an extent. On first run (on
any particular sheet) it come up with an error on the SET and just
breaks the page into 3. on the second run it sets the page breaks.

pretty much what i am tring to do is get it so this macro will be
assigned to a buttn you press the button and regardless of what the
users have done to the print layout it will set it to 3 pages high with
the pages breaks in the correct places.

Sub actuals()

Sheet12.PageSetup.PrintArea = "$A$1:$AQ$215"
With Sheet12.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 3
End With
Sheet12.ResetAllPageBreaks

Sheet12.HPageBreaks(1).Location = Range("A68", "A141")
Sheet12.HPageBreaks(2).Location = Range("A141", "A215")

Set Sheet12.HPageBreaks(1).Location = Range("A68", "A141")
Set Sheet12.HPageBreaks(2).Location = Range("A141", "A215")

End Sub
 
I got a similar result using your code. I got an error on the first Set
Sheet12... line.

Then I modified it as shown below by adding page breaks instead of
settings them, but I still couldn't get it to work. The weird thing is,
I recorded a macro with all the steps and got it to work. When I
manually reset the print area and reset the page breaks, then ran the
macro, I got a big fat nothing. It didn't set the page breaks at all.
Looks like a bug.

' This doesn't work
Sub SetPageBreaks()

Dim ws As Worksheet

Set ws = Application.Sheets("Sheet1")
With ws
With .PageSetup
.PrintArea = "$A$1:$AQ$215"
.FitToPagesWide = 1
.FitToPagesTall = 3
End With
.ResetAllPageBreaks
.HPageBreaks.Add .Range("A68")
.HPageBreaks.Add .Range("A141")

End With
Set ws = Nothing

End Sub
 
whats weirder with mine is if you run it the first time u get error -
run it again everything snaps to the right places LOL
 
Back
Top