Page Breaks in VBA Q

[seanryanie]

I have the macro below to print a range A:HB to 29 pages, problem is however that the Page breaks are not consistent, how can I alter the page breaks within the macro?

Sub Print_Per()
Application.ScreenUpdating = False

Sheets("Month").Select
ActiveSheet.PageSetup.PrintArea = "$A$66:$HB$99"
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.FitToPagesWide = 29
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Range("A1").Select
Sheets("Header").Select
Range("A1").Select

End Sub

 

[Claus Busch]

Hi Sean,

Am Mon, 23 Jul 2012 04:02:39 -0700 (PDT) schrieb (e-mail address removed):

I have the macro below to print a range A:HB to 29 pages, problem is however that the Page breaks are not consistent, how can I alter the page breaks within the macro?

if you have 6 columns per page you will get 30 pages. If you have 7
columns per page you will get 27 pages.

Sub Print_Per()
Dim i As Integer

With Sheets("Month")
With .PageSetup
.PrintArea = "$A$66:$HB$99"
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
End With

.ResetAllPageBreaks
.HPageBreaks.Add .Range("I100")
For i = 1 To 29
.VPageBreaks.Add .Cells(1, i * 7)
Next
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Application.Goto Sheets("Header").Range("A1")
End Sub


Regards
Claus Busch

 

[seanryanie]

Macro as per below, only prints out whats in the sheet "Header", whats in 'Month" and specified range is what I'm after

Sub Print_Per()
Dim i As Integer
Application.ScreenUpdating = False

With Sheets("Month")
With .PageSetup
.PrintArea = "$A$66:$HB$99"
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
End With
.ResetAllPageBreaks
.HPageBreaks.Add .Range("I100")
For i = 1 To 29
.VPageBreaks.Add .Cells(1, i * 7)
Next
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1


Range("A1").Select
Application.Goto Sheets("Header").Range("A1")

End Sub

 

[Claus Busch]

Hi Sean,

Am Mon, 23 Jul 2012 06:48:49 -0700 (PDT) schrieb (e-mail address removed):

Macro as per below, only prints out whats in the sheet "Header", whats in 'Month" and specified range is what I'm after

sorry. Have a try with the new code:

Sub Print_Per()
Dim i As Integer

With Sheets("Month")
With .PageSetup
.PrintArea = "$A$66:$HB$99"
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
End With

.ResetAllPageBreaks
.HPageBreaks.Add .Range("I100")
For i = 1 To 29
.VPageBreaks.Add .Cells(1, i * 7)
Next
.PrintOut Copies:=1
End With

Application.Goto Sheets("Header").Range("A1")
End Sub


Regards
Claus Busch

 

[seanryanie]

Thanks, that worked, but pretty much prints out what I had before.

I want 7 Columns per page (203 columns i.e. A:GU; with 7 per page = 29 pages) , but only 24 pages print out, thus it would appear 1-8 columns print on 1st page, 9-18 columns on 2nd etc

I don't have any hidden columns and each of the batches of 7 columns are similarly sized

 

[Claus Busch]

Hi Sean,

Am Mon, 23 Jul 2012 07:44:38 -0700 (PDT) schrieb (e-mail address removed):

Thanks, that worked, but pretty much prints out what I had before.

I want 7 Columns per page (203 columns i.e. A:GU; with 7 per page = 29 pages) , but only 24 pages print out, thus it would appear 1-8 columns print on 1st page, 9-18 columns on 2nd etc

then change the code:
For i = 1 To 29
.VPageBreaks.Add .Cells(1, i * 8)
Next

with i*8 you have 7 columns per page

Regards
Claus Busch

 

[seanryanie]

That didn't seem to make any difference, still prints to 24 pages.
A:H prints Page 1 (although could be to Col I, as I don't have grid lines shown and Col I is blank)
I:R prints Page 2 (which is 10 columns)

Code as below

Sub Print_Per()
Dim i As Integer
Application.ScreenUpdating = False
With Sheets("Month")
With .PageSetup
.PrintArea = "$A$66:$GU$99"
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
End With
.ResetAllPageBreaks
.HPageBreaks.Add .Range("I100")
For i = 1 To 29
.VPageBreaks.Add .Cells(1, i * 8)
Next
.PrintOut Copies:=1
End With
Application.Goto Sheets("Header").Range("A1")
End sub