Problems With Page Breaks

[ironhydroxide]

I am having problems with the automatic page breaks.

I have written this code and it sets up the column and row widths and
heights and then sets the page breaks where they should be.

My problem is that VBA puts page breaks in also and so that throws off my
printing. the code under the ???? line is my page breaks within a loop.

Thanks for replies

Ironhydroxide


------------------------------------------------------------------------------------------------
Sheets("Picture Board Zones").Select

Do

Cells(1, ColumnLoopOne).ColumnWidth = 2
Cells(1, ColumnLoopTwo).ColumnWidth = 19.14
ColumnLoopOne = ColumnLoopOne + 2
Cells(1, ColumnLoopOne).ColumnWidth = 2
ColumnLoopOne = ColumnLoopOne + 1
ColumnLoopTwo = ColumnLoopTwo + 3
Loop Until ColumnLoopOne > 18

????????????????????????????????????????????????????????????????????????????????
ActiveSheet.VPageBreaks.Add Before:=Range("S1")

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.RightFooter = "Printed On " & Date
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
End With

Do
Cells(RowLoopOne, 1).EntireRow.RowHeight = 9.75
Cells(RowLoopTwo, 1).EntireRow.RowHeight = 146.25
Cells(RowLoopThree, 1).EntireRow.RowHeight = 38.25
Cells(RowLoopFour, 1).EntireRow.RowHeight = 18.75
RowLoopOne = RowLoopOne + 4
RowLoopTwo = RowLoopTwo + 5
RowLoopThree = RowLoopThree + 5
RowLoopFour = RowLoopFour + 5
Rows(RowLoopOne).RowHeight = 9.75

If RowLoopOne = 15 Or RowLoopOne = 30 Or RowLoopOne = 45 Or
RowLoopOne = 60 Or RowLoopOne = 75 Or RowLoopOne = 90 Or RowLoopOne = 105 Or
RowLoopOne = 120 Or RowLoopOne = 135 Or RowLoopOne = 150 Or RowLoopOne = 165
Or RowLoopOne = 180 Or RowLoopOne = 195 Then

????????????????????????????????????????????????????????????????????????????????
ActiveSheet.HPageBreaks.Add Before:=Range("A" & (RowLoopOne + 1))
End If
RowLoopOne = RowLoopOne + 1

Loop Until RowLoopOne > 200

ColumnLoopOne = 1
ColumnLoopTwo = 2
RowLoopOne = 1
RowLoopTwo = 2
RowLoopThree = 3
RowLoopFour = 4
Sheets("Picture Board ZoneAV").Select
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Do

Cells(1, ColumnLoopOne).ColumnWidth = 2
Cells(1, ColumnLoopTwo).ColumnWidth = 19.14
ColumnLoopOne = ColumnLoopOne + 2
Cells(1, ColumnLoopOne).ColumnWidth = 2
ColumnLoopOne = ColumnLoopOne + 1
ColumnLoopTwo = ColumnLoopTwo + 3
Loop Until ColumnLoopOne > 18

????????????????????????????????????????????????????????????????????????????????
ActiveSheet.VPageBreaks.Add Before:=Range("S1")

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.RightFooter = "Printed On " & Date
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$R$200"
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
BreakNum = 1
Do
Cells(RowLoopOne, 1).EntireRow.RowHeight = 9.75
Cells(RowLoopTwo, 1).EntireRow.RowHeight = 146.25
Cells(RowLoopThree, 1).EntireRow.RowHeight = 38.25
Cells(RowLoopFour, 1).EntireRow.RowHeight = 18.75
RowLoopOne = RowLoopOne + 4
RowLoopTwo = RowLoopTwo + 5
RowLoopThree = RowLoopThree + 5
RowLoopFour = RowLoopFour + 5
Rows(RowLoopOne).RowHeight = 9.75

If RowLoopOne = 15 Or RowLoopOne = 30 Or RowLoopOne = 45 Or RowLoopOne =
60 Or RowLoopOne = 75 Or RowLoopOne = 90 Or RowLoopOne = 105 Or RowLoopOne =
120 Or RowLoopOne = 135 Or RowLoopOne = 150 Or RowLoopOne = 165 Or RowLoopOne
= 180 Or RowLoopOne = 195 Then
????????????????????????????????????????????????????????????????????????????????
ActiveSheet.HPageBreaks.Add Before:=Range("A" & (RowLoopOne + 1))
End If
RowLoopOne = RowLoopOne + 1
Loop Until RowLoopOne > 200
------------------------------------------------------------------------------------------------

 

[Joel]

You page breaks have to be inside the margins for the page. Change the Top
and bottom margins on the page

With ActiveSheet.PageSetup
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(1.5)
End With

 

[ironhydroxide]

Tried changing the bottom margin to 1.5 and it did nothing as to what i
wanted it to do.
I feel that you have misunderstood me, i will reexplain

So I want to have the page breaks where i put them in the previously posted
code clip. when i do run this code i get rogue automatic page breaks within
my set (and wanted) page breaks, turning my one page into 4 seperate pages.
these are the page breaks that i am having problems removing.

thanks for posting

Ironhydroxide

 

[Joel]

You can't turn off automatic page breaks. Excel adds breaks whenever you
exceed the margins of the page. You must put your page breaks into the
worksheet before the automatic breaks take place so you don't get extra page
breaks.

The usable area of the page is 11" - (top margin + bottom margin)

You may have to make the top and botom margins smaller or put less rows on
each page.

 

[ironhydroxide]

that is kinda annoying.

I think i fixed it by recording a macro of me dragging the auto page breaks
to the side and all the way down (this throws off my print size percentage,
but i fix that with pagesetup.zoom=xx).
I had just hoped that there was an easier way to do this.

Thanks though

 

[Joel]

I don;t think the dragging of the breaks did anything except gave you the
idea to scale the page. The dragging of the breaks only temporarily fixed
the problem. The breaks would of gone back to there original position when
the page got recalculate. You effectively did what I said by scaling the
page. You moved the page breaks inside the margins of the page. The scaling
did change to margins but it did change
the row height