none
Print Selected number of rows on a sheet on every page RRS feed

  • Question

  • This is a long shot, I'm not much into the developer section of excel, but I have a worksheet like the one in the picture bellow. The goal is to get the bottom section starting with the divider

    and the August 2014 Records Information all the way down to the last cell with Insulin average to stay or print on all pages. The table will grow with more and more data requiring more pages so I want to keep the bottom section on all of the pages.

    I thought about using a header and footer, but I don't know of any way of making them look as nice as the top and bottom of this worksheet currently looks.

    I have a VB application that is making the entire page (formatting and entering the data). This  being said, whatever would best work in my situation needs to be explained with all steps so I can convert that into code in the program.

    • Edited by Brettman21 Wednesday, May 4, 2016 2:41 PM Removed image due to sensitive information/formatting
    Wednesday, March 9, 2016 3:34 PM

Answers

  • The simplest thing would be to have another column in your table, off the printed area, that includes the page that you want to place the value on. For example, say that you decide that you can have 10 rows per page. In cell G5, enter the formula

    =INT((ROW()-5)/10)+1

    Where the 10 is the number of rows to show per printed page, then copy down to match all of your data table.

    Then in your macro, filter your table based on column G to show 1, print the sheet, filter to show 2, print, etc... that is an easy loop to set up, and you will get the results you want.


    Wednesday, March 9, 2016 5:31 PM

All replies

  • The simplest thing would be to have another column in your table, off the printed area, that includes the page that you want to place the value on. For example, say that you decide that you can have 10 rows per page. In cell G5, enter the formula

    =INT((ROW()-5)/10)+1

    Where the 10 is the number of rows to show per printed page, then copy down to match all of your data table.

    Then in your macro, filter your table based on column G to show 1, print the sheet, filter to show 2, print, etc... that is an easy loop to set up, and you will get the results you want.


    Wednesday, March 9, 2016 5:31 PM
  • Hi, Brettman21

    According to your description, please correct me if I have any misunderstandings on your question, I suggest that you could hide rows, columns or cells when you print a sheet:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        If ActiveSheet.Name = "Sheet1" Then
            Cancel = True
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            With ActiveSheet
                .Rows("10:15").EntireRow.Hidden = True
                .PrintOut
                .Rows("10:15").EntireRow.Hidden = False
            End With
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub

    Thursday, March 10, 2016 3:03 AM
  • Re:  print the same rows on each page.

    Rows to repeat at top in PageSetUp will do that.
    The repeating rows will be at the top of each page, and Excel will do that automatically for each page.
    That should make it something to consider.

    '---
    Jim Cone
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 10:53 PM
    Thursday, March 10, 2016 5:36 AM
  • Hi, Brettman21

    According to your description, please correct me if I have any misunderstandings on your question, I suggest that you could hide rows, columns or cells when you print a sheet:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        If ActiveSheet.Name = "Sheet1" Then
            Cancel = True
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            With ActiveSheet
                .Rows("10:15").EntireRow.Hidden = True
                .PrintOut
                .Rows("10:15").EntireRow.Hidden = False
            End With
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub


    I am not looking to hide any rows, I am looking to print a select few rows on every singe page.? A16 to F25 are the cells I want to have printed at the bottom of each page. If this makes more sense to you?

    Thursday, March 10, 2016 1:19 PM
  • Re:  print the same rows on each page.

    Rows to repeat at top in PageSetUp will do that.
    The repeating rows will be at the top of each page, and Excel will do that automatically for each page.
    That should make it something to consider.

    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel add-in programs  (xl97 to xl2010 only)
    http://jmp.sh/K95N3ee
    I am actually using that to repeat the top of the sheet. But the rows I want to also have repeated on each page are at the bottom of the sheet. A16 to F25. Is there any feature that will repeat bottom rows on every page?
    Thursday, March 10, 2016 1:20 PM
  • Re:  "Is there any feature that will repeat bottom rows on every page?"

    Excel does not provide that capability as far as I know.

    But...  [EDIT] Not offered any more
    If you are using xl97 thru xl2010, my commercial Rows Repeat_Bottom add-in might work for you.
    Some early users could not get the proper display of rows.  I revised the program, but gave up on offering it to the public again.
    If your data...
      does not extend beyond the first verticalpage break,
      has no merged cells
      row height does not vary excessively
    it could work for you.

    It creates a new worksheet with the repeating rows added - you print from the new sheet.
    3 week trial, no ads, no trackers, no cookies, no registration
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 10:57 PM
    Thursday, March 10, 2016 2:59 PM