PDA

View Full Version : Adapting page break



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!

mikerickson
03-06-2012, 11:46 AM
Perhaps
If nRows > 40 Then
nPagebreaks = Int(nRows / 50)
For i = 0 To nPagebreaks
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(50 * i + 40, 1)
Next i
End If

marreco
03-06-2012, 12:12 PM
I'll try and then return.

Thanks for responding

marreco
03-06-2012, 12:47 PM
It turns out that all the page break is 50 to 50 lines.
I wish the 1st page break to go to the line 40 after that the other page break was 50 by 50 lines.

Can you do that?:help

Thank you!

mikerickson
03-06-2012, 01:03 PM
Did you change the i = 0 to nPageBreaks

marreco
03-06-2012, 02:26 PM
Forgive me foolish but I do not understand.
switch to ...
Did you change the i = 1 to nPageBreaks??

mikerickson
03-06-2012, 04:21 PM
The loop needs to start with 0

marreco
03-06-2012, 04:39 PM
I did so but failed.

What did I do wrong?:dunno

Option Explicit

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
'adicionar quebra de página a cada 40 linhas
nRows = R.Rows.Count
If nRows > 40 Then
nPagebreaks = Int(nRows / 40)
For i = 0 To nPagebreaks
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(40 * i + 1, 1)
Next i
End If
'pode ser usado numa macro separada, Iníciando a contagem do número de quebras de página
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
' A célula na coluna 1 e na linha imediatamente abaixo da página quebrada
' Contém o texto para o rodapé
ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)
' ActiveSheet.PrintOut copies:=1
Next i
End Sub

mikerickson
03-06-2012, 06:55 PM
That does not use the code that I posted in post#2.
Actualy there was an error in that code, use this instead

If nRows > 40 Then
nPagebreaks = Int(nRows / 50)
For i = 0 To nPagebreaks
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveSheet.Cells(50 * i + 41, 1)
Next i
End If

marreco
03-06-2012, 07:05 PM
Hi.
I am very silly and did not understand.:mkay
You mean to use the code this way?:doh:


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
'adicionar quebra de página a cada 40 linhas
nRows = R.Rows.Count
If nRows > 40 Then
nPagebreaks = Int(nRows / 50)
For i = 0 To nPagebreaks
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveSheet.Cells(50 * i + 41, 1)
Next i
End If

'pode ser usado numa macro separada, Iníciando a contagem do número de quebras de página
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
' A célula na coluna 1 e na linha imediatamente abaixo da página quebrada
' Contém o texto para o rodapé
ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)
' ActiveSheet.PrintOut copies:=1
Next i
End Sub

mikerickson
03-06-2012, 07:15 PM
Yes

marreco
03-06-2012, 07:21 PM
Continues to break of 50 to 50.
Thanks for your patience.

Let's leave it at that, I can not mark as solved if you can dial, I'm very grateful.

Thank you!