Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rows to repeat at Bottom (2000)

    My daughter has many spreadsheets where she wants to repeat the same four lines of text at the bottom of each page when printing. (They form a 'key' to status codes in the printed lists.) At present, the text is in the four cells A242:A245.

    Obviously, she could move them to A1:A4 and use ROWS TO REPEAT AT TOP, but she would prefer the explanation to appear at the end of each page, if possible. Custom footer is no good, as the text is over 255 characters long. She is printing in Landscape, because of the length of one of the keys.

    I have used search, but not come up with anything, and would appreciate any help.

    Tony
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rows to repeat at Bottom (2000)

    There is no built-in way to do this.

    Some options:
    If you have xl2002+ with builtin support to add a graphic to the footer, you could create a "picture" of the text, add it to the footer and put it on each page.

    Copy the table to WORD, which has this capability

    Abbreviate the text to have only 255 total chars for the footer and put it into the footer

    Manually insert copies of the lines before every page break

    Create a macro to add the text (as either new lines, a text box, a picture whatever) at the end of every line before printing (how this will be done will depend on setup, and how often it needs to be changed)

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows to repeat at Bottom (2000)

    Steve:

    Thanks for your reply, and the suggested solutions.

    The company she works for doesn't have 2002, so that one's out.

    The text is fixed, so the 'less than 255' is not an option.

    I thought of repeating the cells down the worksheet, but that would be upset when rows were inserted.

    Copying to Word is a possibility, although she has about 150 files.

    The macro option looks most promising. Is it possible in Excel to write code to insert four lines at the bottom of each page at print-time? The 4 lines of text will be constant, so I guess could be read from cells or hard-coded into the procedure. Would the macro have to count the number of lines that have been printed (if that can be done)?

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rows to repeat at Bottom (2000)

    Concerning the macro,
    I "imagine" (I have not worked or tried any detail coding) that you could (before printing)
    use the location property of each HPageBreak object to determine where the breaks are, then insert the "4 rows" , 4 rows above this location
    Print the "new sheet"
    Reverse the process by removing the 4 rows before the page breaks.

    If you wanted the last page to be at the bottom, you would need to place the rows in the correct position.

    You could add a macro to the before print routine, check the worksheet and (if the correct one) call the routine, if not then print regularly. (if there is not an easy test, you could ask the user if they wanted the "bottom titles" printed anytime print was pressed.

    Hope this helps, If you need more details, let us know.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows to repeat at Bottom (2000)

    Thanks, Steve.

    Before I embark on this major (for me) project, if the procedure is attached to the before print event, will that affect other files if this file is open, all other files regardless or just this file?

    Since my last post I had the lateral idea of printing the footer lines to a blank sheet, then printing the list on that, with an increased lower margin. Unfortunately, she is using a networked printer and has no way of stopping everyone else from printing after loading the pre-printed sheets.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rows to repeat at Bottom (2000)

    The print event is for the (one) Workbook. Anytime you try to print in the workbook (no matter what the sheet), the "beforeprint" event is triggered.

    I imagined a routine to do the actual "manipulate", print, put back (perhaps, just creating a copy, change that sheet, print it, then delete it might be the easiest: you never change the original).

    I imagine in the "beforeprint" event to have some "check" (what exactly depends on your needs) to see if the "Manipulate/print routine" would be called or just have excel print. This check, could look at the sheet to print to see if it is the one (any other would just print normal) or even additionally, it could prompt (if it were that sheet) for "normal" or with "bottom titles" and run the macro or print normal.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows to repeat at Bottom (2000)

    Steve:

    Thanks for your help. You may hear from me again if I hit problems!

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rows to repeat at Bottom (2000)

    The board is always open, come back as needed. We are always willing to help.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •