Put data rows per sheet

G

girapas

I have an Excel workbook with 2 sheets. One of them is for data entering
(e.g. rows: employee's name, ID#, working dates, fee etc.) and the other
presents that data among other constant value cells (e.g. name of company,
titles of columns, place for signatures etc.). What I want is when data in
the presentation sheet reach at 22 rows to be automatically continued to the
next page of the sheet. So, each printed page will always shows 22 rows of
data with the constant data ABOVE and BELOW of these 22 rows. Thanks,
 
F

Frank Kabel

Hi
not fully automatic but try the following two macros (assumption: row 1
is a heading row and you have setup the page that row 1 is repeated on
each page 'File - Pagesetup - Sheet'):

Option Explicit
Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For row_index = 23 to lastrow - 1 Step 22
ActiveSheet.HPageBreaks.Add Before:= _
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub
 
R

Ron de Bruin

Typo

Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = 23 To lastrow - 1 Step 22
ActiveSheet.HPageBreaks.Add Before:=Cells(row_index, 1)
Next
End Sub
 
G

girapas

Thank you Frank and Ron for your help.




Ron de Bruin said:
Typo

Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = 23 To lastrow - 1 Step 22
ActiveSheet.HPageBreaks.Add Before:=Cells(row_index, 1)
Next
End Sub
Click to expand...
 
Top