Page Breaks in VBA Q

  • Thread starter Thread starter seanryanie
  • Start date Start date
S

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
 
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?
Click to expand...

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
 
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
 
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
Click to expand...

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
 
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
 
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
Click to expand...

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
 
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
 
Back
Top