need "print titles" function but working on the bottom of the page

[yky]

I have a form whose row 1 to row 5 and row 18 to row 20 do not change. What is changed is the data in row 6 to row 17.

If I have data in, say, 30 rows, how do I print out the data on three pages (12 rows - row 6 to row 17- of data per page) and have the same row 1 to 5 and 18 to 20 on each page?

For row 1 to row 5, I can use the "page layout/print titles" function and set "rows to repeat at top" to "$1:$5". What about the bottom part?

Thanks in advance for any help.

 

[iliace]

There is no good way to do this in Excel. I would recommend to use a program such as Word for your printing layout, and perform a mail merge from an Excel data set.

 

[Peter_SSs]

I agree that this is not simple in Excel.

However, you could give this a try in a copy of your workbook. It may work otherwise, but I am assuming that you only have a single worksheet in the workbook. If there are more, and you want different behaviour when printing the others, it may be possible to modify this to suit, so long as this sheet of interest is never printed as part of a group of worksheets.

The concept is to copy the worksheet and on that 'print' copy move the "rows to repeat at the bottom" to the bottom of the worksheet, hide all the "middle" rows, then unhide those middle rows in suitably sized groups and print the worksheet. Repeat the hide/unhide until all the "middle" rows have been printed then delete this 'print' worksheet.

Edit the 3 "Const" lines in the code to suit your needs.

This code goes in the ThisWorkbook module in the VB window. Once there, Print (or PrintPreview) the worksheet. Whatever you choose, I have set it to PrintPreview. After viewing the PrintPreview close it and the next one should pop up.
If satisfied, then change the PrintPreview line in the code to PrintOut & test again.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Dim wsPrint As Worksheet
  Dim lr As Long, fr As Long, r As Long
  
  Const sHeaderRows As String = "1:5"
  Const sFooterRows As String = "18:20"
  Const lBodyRows As Long = 20
  
  Application.ScreenUpdating = False
  Cancel = True
  ActiveSheet.Copy After:=ActiveSheet
  Set wsPrint = ActiveSheet
  With wsPrint
    fr = .Rows(sHeaderRows).Rows.Count + 1
    lr = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, _
      SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    With .Rows(sFooterRows)
      .Cut Destination:=.Parent.Cells(lr + 1, 1)
      lr = lr - .Rows.Count
    End With
    .Rows(sFooterRows).Delete
    Application.EnableEvents = False
    For r = fr To lr Step lBodyRows
      .Rows(fr).Resize(lr - fr + 1).Hidden = True
      .Rows(r).Resize(lBodyRows).Hidden = False
      .PrintPreview '.PrintOut
    Next r
    Application.EnableEvents = True
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
  End With
  Application.ScreenUpdating = True
End Sub

 

[yky]

Thank you for the code. It is quite clever to hide and unhide middle rows to show and print them in the desired manner.

 

[Peter_SSs]

No problem. Thank you for the feedback.