| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems With Page Breaks
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 Befo=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 Befo=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 Befo=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 Befo=Range("A" & (RowLoopOne + 1)) End If RowLoopOne = RowLoopOne + 1 Loop Until RowLoopOne 200 ------------------------------------------------------------------------------------------------ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems With Page Breaks
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" wrote: 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 Befo=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 Befo=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 Befo=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 Befo=Range("A" & (RowLoopOne + 1)) End If RowLoopOne = RowLoopOne + 1 Loop Until RowLoopOne 200 ------------------------------------------------------------------------------------------------ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems With Page Breaks
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" wrote: 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" wrote: 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 Befo=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 Befo=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 Befo=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 Befo=Range("A" & (RowLoopOne + 1)) End If RowLoopOne = RowLoopOne + 1 Loop Until RowLoopOne 200 ------------------------------------------------------------------------------------------------ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems With Page Breaks
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" wrote: 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" wrote: 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" wrote: 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 Befo=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 Befo=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 Befo=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 Befo=Range("A" & (RowLoopOne + 1)) End If RowLoopOne = RowLoopOne + 1 Loop Until RowLoopOne 200 ------------------------------------------------------------------------------------------------ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems With Page Breaks
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 Befo=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 Befo=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 Befo=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 Befo=Range("A" & (RowLoopOne + 1)) End If RowLoopOne = RowLoopOne + 1 Loop Until RowLoopOne 200 ------------------------------------------------------------------------------------------------ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems With Page Breaks
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 "ironhydroxide" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 Befo=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 Befo=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 Befo=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 Befo=Range("A" & (RowLoopOne + 1)) End If RowLoopOne = RowLoopOne + 1 Loop Until RowLoopOne 200 ------------------------------------------------------------------------------------------------ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I can't move my page breaks in Page Break Preview | Excel Discussion (Misc queries) | |||
| When automatic page breaks are moved each cell becomes a new page | Excel Discussion (Misc queries) | |||
| Vertical page breaks won't drag in Page Break Preview | Excel Discussion (Misc queries) | |||
| How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
| Page Breaks- Printing selected rows on same page | Excel Discussion (Misc queries) | |||
Linear Mode
