Make page break every several lines automatically by VBA
 

Hello, is it possible to make page break by counting lines? I've
written a program to create graphs with comments. But every time EXCEL
just separate the page by the length of graphs. What I want is to make
page break between every 39 line and 40 line automatically. For
example, between 39 and 40, 78 and 79...etc.

By the way, the length of the graphs I created changes in a very little
range. But it is only the little change that make me cannot print
correctly.

Make page break every several lines automatically by VBA
 

Something like this :

Private Sub CommandButton1_Click()
Const HPageBreakSpacing As Long = 40
Const PagesToProcess As Long = 4
Dim WS As Worksheet
Dim i As Long

Set WS = ThisWorkbook.Worksheets(1)
With WS
.ResetAllPageBreaks
For i = 1 To PagesToProcess
.HPageBreaks.Add Cells(i * HPageBreakSpacing, 1)
Next
End With
End Sub

NickHK

wrote in message
ps.com...
Hello, is it possible to make page break by counting lines? I've
written a program to create graphs with comments. But every time EXCEL
just separate the page by the length of graphs. What I want is to make
page break between every 39 line and 40 line automatically. For
example, between 39 and 40, 78 and 79...etc.

By the way, the length of the graphs I created changes in a very little
range. But it is only the little change that make me cannot print
correctly.

Make page break every several lines automatically by VBA
 

how about something like this. just change the range and sheet names

Sub test()
Dim cell As Range
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
For Each cell In Range("a1:A400")
With ws
If cell.Row Mod 39 = 0 Then
.HPageBreaks.Add befo=cell.Offset(1, 0)
End If
End With
Next
End Sub

--


Gary


wrote in message
ps.com...
Hello, is it possible to make page break by counting lines? I've
written a program to create graphs with comments. But every time EXCEL
just separate the page by the length of graphs. What I want is to make
page break between every 39 line and 40 line automatically. For
example, between 39 and 40, 78 and 79...etc.

By the way, the length of the graphs I created changes in a very little
range. But it is only the little change that make me cannot print
correctly.

Make page break every several lines automatically by VBA
 

Thank you very much, I've tried and it really works.

I am so surprised that your response comes so fast.

On 11ÔÂ9ÈÕ, ÏÂÎç2ʱ39·Ö, "NickHK" wrote:
Something like this :

Private Sub CommandButton1_Click()
Const HPageBreakSpacing As Long = 40
Const PagesToProcess As Long = 4
Dim WS As Worksheet
Dim i As Long

Set WS = ThisWorkbook.Worksheets(1)
With WS
.ResetAllPageBreaks
For i = 1 To PagesToProcess
.HPageBreaks.Add Cells(i * HPageBreakSpacing, 1)
Next
End With
End Sub

NickHK

wrote in glegroups.com...



Hello, is it possible to make page break by counting lines? I've
written a program to create graphs with comments. But every time EXCEL
just separate the page by the length of graphs. What I want is to make
page break between every 39 line and 40 line automatically. For
example, between 39 and 40, 78 and 79...etc.

By the way, the length of the graphs I created changes in a very little
range. But it is only the little change that make me cannot print
correctly.- Òþ²Ø±»ÒýÓÃÎÄ×Ö -- ÏÔʾÒýÓõÄÎÄ×Ö -