Setting page breaks in a print macro

[countingapples]

:hi: Hi Gang! Well it's my turn for asking a question.

I'm working on some print macros for multiple workbooks. One macro does the protraits and the other does the landscapes. Where I've run into trouble is in setting the page breaks. I've not had to deal with them before, but now I do. I will need to be able to set both horizontal and vertical page breaks. Any help will be most appreciated. The code I'm trying to modify for both are below.
Thanks.


Option Explicit

Sub RawProd_Portrait()
' RawProd_Portrait Macro
Columns("S:AD").Select
Selection.EntireColumn.Hidden = True
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "A1:R112"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&10&F &A"
.CenterFooter = "&10&D &T"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
End Sub


Sub RawProd_Landscape()
' RawProd_Landscape Macro
Columns("S:AD").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "A1:AD112"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&10&F &A"
.CenterFooter = "&10&D &T"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.32)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
Range("A1").Select
End Sub

[austenr]

Try this link whick explaines adding page breaks. HTH



http://www.exceltip.com/st/Add_page_breaks_to_sorted_data_using_VBA_in_Microsoft_Excel/635.html

[Ken Puls]

Hi Karen,

Working off memory, which isn't always so good for me, I seem to remember that the zoom property could affect the way that page breaks came out too. Just to keep in mind in case the link that Austen provided for you doesn't seem to work.

Also, you've got a lot of extraneous stuff in your pagesetup macro. You may want to think about culling some of the stuff which is always going to be set by default. I'm thinking that lines like .BlackAndWhite = False are probably not necessary, although you know your printer better than I do! :D

Cheers,

[countingapples]

Austen: thanks for the link, I'm playing around with it.

Ken: Didn't you know that the recorder always gives you extras? :*) I'll clean it all up when I fully understand which I really do need and which I can ditch. I'll keep the note about the zoom in mind.

Thanks guys. I'll let you know how I end up. :thumb

[Ken Puls]

Cheers, Apples!

I did know, and thought you did too, but just wanted to make sure! :)

[countingapples]

Worked like a charm Austen, thanks for the link. :thumb