Make page break every several lines automatically by VBA

[ombreluna]

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.

 

[NickHK]

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

 

[Gary Keramidas]

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 before:=cell.Offset(1, 0)
End If
End With
Next
End Sub

 

[ombreluna]

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

I am so surprised that your response comes so fast.