marreco
03-06-2012, 05:50 AM
I'd like to break the page as follows.
So I'd like to break the page in line 40, after a break of 50 page 50 lines.
Sub PrintAreaWithpageBreaks()
Dim pages As Integer
Dim pageBegin As String
Dim PrArea As String
Dim i As Integer
Dim q As Integer
Dim nRows As Integer, nPagebreaks As Integer
Dim R As Range
Set R = ActiveSheet.UsedRange
'add pagebreak every 40 rows
nRows = R.Rows.Count
If nRows > 40 Then
nPagebreaks = Int(nRows / 40)
For i = 1 To nPagebreaks
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(40 * i + 1, 1)
Next i
End If
'can be used in a separate macro, as I Start counting the number of pagebreaks
pages = ActiveSheet.HPageBreaks.Count
pageBegin = "$A$1"
For i = 1 To pages
If i > 1 Then pageBegin = ActiveSheet.HPageBreaks(i - 1).Location.Address
q = ActiveSheet.HPageBreaks(i).Location.Row - 1
PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q))
ActiveSheet.PageSetup.PrintArea = PrArea
' the cell in column 1 and in the row immediately below the pagebreak
' contains text for the footer
ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)
' ActiveSheet.PrintOut copies:=1
Next i
End Sub
so ...
assuming I want it to break page with 40 lines, but from the second, each is 50, how would the above code?
can anyone help me?
Cross-Post
http://www.excelforum.com/excel-programming/818103-adapting-page-break.html
Thank you!
So I'd like to break the page in line 40, after a break of 50 page 50 lines.
Sub PrintAreaWithpageBreaks()
Dim pages As Integer
Dim pageBegin As String
Dim PrArea As String
Dim i As Integer
Dim q As Integer
Dim nRows As Integer, nPagebreaks As Integer
Dim R As Range
Set R = ActiveSheet.UsedRange
'add pagebreak every 40 rows
nRows = R.Rows.Count
If nRows > 40 Then
nPagebreaks = Int(nRows / 40)
For i = 1 To nPagebreaks
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(40 * i + 1, 1)
Next i
End If
'can be used in a separate macro, as I Start counting the number of pagebreaks
pages = ActiveSheet.HPageBreaks.Count
pageBegin = "$A$1"
For i = 1 To pages
If i > 1 Then pageBegin = ActiveSheet.HPageBreaks(i - 1).Location.Address
q = ActiveSheet.HPageBreaks(i).Location.Row - 1
PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q))
ActiveSheet.PageSetup.PrintArea = PrArea
' the cell in column 1 and in the row immediately below the pagebreak
' contains text for the footer
ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)
' ActiveSheet.PrintOut copies:=1
Next i
End Sub
so ...
assuming I want it to break page with 40 lines, but from the second, each is 50, how would the above code?
can anyone help me?
Cross-Post
http://www.excelforum.com/excel-programming/818103-adapting-page-break.html
Thank you!