+ Reply to Thread
Results 1 to 32 of 32

How to force text to print at the bottom of the last page of an excel

  1. #1
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Question How to force text to print at the bottom of the last page of an excel

    Cross-posted at
    https://www.mrexcel.com/board/thread...rinted.1200879
    https://forum.ozgrid.com/forum/index...at-is-printed/

    Hello all and have a nice Month!


    I have a worksheet that works with tables and I filling it with data from other sheets. How can I force text to be printed at the bottom of the last page of the sheet that is going to be printed. I dont want it to be placed in footer but at the same page (last page, the print area will be 1 or 2 or 3 or even more).*

    Furthermore, Can I grab data from another sheet and place them at the bottom? Not a single text , but maybe 4-5 lines.

    I attach sample file. DATASET is where I grab the data, FORM is where placed the data, TEST1 are the data that FORM collects.

    Thank you !
    Attached Files Attached Files
    Last edited by 6StringJazzer; 04-01-2022 at 09:43 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    East Asia
    MS-Off Ver
    Office 365
    Posts
    311

    Re: How to force text to print at the bottom of the last page of an excel

    Not entirely clear what is being printed. Assuming it's TEST1, you can place the additional lines below the table (say rows 140:144 for example).

    To have it print on the last page, select the range of your table and the additional lines (Cells B2:F144), then go to Print > Print Selection.

    Does that work / have I understood right?
    <<< Please click * to thank valued contributors

  3. #3
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Also posted at forum.ozgrid.com/forum/index.php?thread/1231329-add-text-at-the-last-row-of-the-last-page-of-what-is-printed/&postID=1255872#post1255872 and mrexcel.com/board/threads/add-text-at-the-last-row-printed.1200879/page-2#post-5861890

  4. #4
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Quote Originally Posted by AskMeAboutExcel View Post
    Not entirely clear what is being printed. Assuming it's TEST1, you can place the additional lines below the table (say rows 140:144 for example).

    To have it print on the last page, select the range of your table and the additional lines (Cells B2:F144), then go to Print > Print Selection.

    Does that work / have I understood right?
    I understand what you are talking about. I need this to be done with VBA. What is printed is FORM

    I can add text under the last row of the table at BuildInvoiceAll using this code




    Please Login or Register  to view this content.
    But I was thinking if it can be addedd at the last page of the invoice (FORM), above footer , I mean at the last line and not exactly under the table.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,131

    Re: How to force text to print at the bottom of the last page of an excel

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    I have added the links for you because you are a new member.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Yes. I know... my mistake. Thank you very much !

  7. #7
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Update! Finally I will do that with footers using this code

    ThisWorkbook.ActiveSheet.PageSetup.LeftFooter = or RightFooter etc.

    Thank you all for your effort and time wasted !

    Cheers!

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,131

    Re: How to force text to print at the bottom of the last page of an excel

    Does that really do what you want? It will print a footer on every page, not just the last one.

  9. #9
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44
    Quote Originally Posted by 6StringJazzer View Post
    Does that really do what you want? It will print a footer on every page, not just the last one.
    Somehow yes, I think that I have found a way to only print at last page. Do you have any other suggestion?

  10. #10
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    I think I have found a solution. I don't know of any other method for finding the end of the last page other than testing by enlarging a PrintArea. And that takes a while. But in return, I increased the speed of copying the rows from the Test1 sheet.
    Please Login or Register  to view this content.
    The code may be a bit inconsistent, but it works on test data.

    ..::Edit:
    I forgot to mention that the width of the C column in the FORM sheet cannot be smaller than in the DATASET sheet. Another solution might be to remove the text wrapping in column C in the DATASET worksheet. ::..

    Artik
    Last edited by Artik; 04-03-2022 at 05:42 AM.

  11. #11
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Quote Originally Posted by Artik View Post
    I think I have found a solution. I don't know of any other method for finding the end of the last page other than testing by enlarging a PrintArea. And that takes a while. But in return, I increased the speed of copying the rows from the Test1 sheet.
    Please Login or Register  to view this content.
    The code may be a bit inconsistent, but it works on test data.

    ..::Edit:
    I forgot to mention that the width of the C column in the FORM sheet cannot be smaller than in the DATASET sheet. Another solution might be to remove the text wrapping in column C in the DATASET worksheet. ::..

    Artik
    Finally I tried your solution but excel crashed. A lot of X , I dont know what gone wrong

  12. #12
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    I tested the macro all the time in page split view. You ran the macro in normal view. Below is the corrected code:
    Please Login or Register  to view this content.
    Artik

  13. #13
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Thank you @Artik for your responce !
    I forgot to mention that I have changed the InvoiceDryhire only the way that collect data. Its Perfect and is working FINE !!! Please make it to work at my new code!
    The new code is

    Please Login or Register  to view this content.
    And I have attached and sample file.

    Thank you @Artik !
    Attached Files Attached Files
    Last edited by gerasimos; 04-20-2022 at 03:35 PM.

  14. #14
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    Non-optimized code:
    Please Login or Register  to view this content.
    Artik

  15. #15
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    Quote Originally Posted by Artik View Post
    Non-optimized code:

    unnecessarily quoted code removed by mod
    @Artik THANK YOU ! really thank you!
    To me it seems that the code is ok, what do you mean optimized? The only thing I saw is that sometimes when the CODESET when inserted at the second page it isnt at the bottom but head, I think that has to do with the lines of calculations etc. Can this be at the bottom? Even if there is no lines at the head?
    If it is a minor update

    THANK you ! Appreciate that !
    Last edited by 6StringJazzer; 04-21-2022 at 02:44 PM.

  16. #16
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    In the future, please don't quote your predecessor's entire statement, as you are littering the forum unnecessarily. If necessary, quote only fragments.

    Quote Originally Posted by gerasimos View Post
    what do you mean optimized?
    If the macro execution time is acceptable to you, then don't bother.

    Below is the version you asked for.
    Please Login or Register  to view this content.
    Artik

  17. #17
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik I am very sorry about that, the quote, but I was really happy about your reply, Never will happen again.

    The final solution working like a charm ! Thank you again for your effort and everything !

    Gerasimos

  18. #18
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik Hello again,

    Is it possible to fill the empty rows until the end with border, black color ?

    Thank you again and again and again ...!

  19. #19
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    Please Login or Register  to view this content.
    Artik

  20. #20
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    wow! @Artik You Are Fast and always to the point !!!

    Thank you very very much !!!

  21. #21
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik I now that I ask too much, but I tried to make the rows that are made with constant height, but cant do that. Can you please help me? I need the table "table3" to have constant row height, everything I tried not working (I tried.rowheight etc) and also the rows that are crearted to have constant height also, lets say 30p .


    Or how can I change row height at a table at keep it constan for every row that is made (I hope you understand, my English are not that good)
    Is that possible?

    Thank you very much
    Gerasimos

  22. #22
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    Try adding the highlighted line in this code snippet:
    Please Login or Register  to view this content.
    Artik

  23. #23
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik thank you very much for your answer!
    Last edited by gerasimos; 05-04-2022 at 01:57 PM.

  24. #24
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik and thank you for your quick reply !
    Last edited by gerasimos; 05-04-2022 at 01:58 PM.

  25. #25
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik a question for not bother you all the time. If I change font size the copied text dont placed at the bottom of the page. What do I have to change at the code? Is it something simple? Like a variable?

    Thank you
    Gerasimos

  26. #26
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    Please show an example in the attachment that does NOT work properly, because I don't understand the problem.

    Artik

  27. #27
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik Here is the sample

    What I did is that I addedd

    Please Login or Register  to view this content.
    in order to change font size (18 or 20) to all worksheet

    Then I added
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    In order to make row height 32 to all worksheet.

    Then I built invoice, working fine but I can make DATASET to be placed at the bottom of the last page

    This one was working perfect, but something broken when I changed row height. I think there is the problem , but I cant find where

    This is what I am asking what can I do and also if want to chage height what should I change to make it work again.

    I also tried to make the cell E15 not be visible (and at every cell that has sheet name as yellow), I thinking of change cell font color to white but it changed every font color at E

    Thats all I have done. Can you please help me and show me how to do it?

    Thank you
    Gerasimos
    Attached Files Attached Files
    Last edited by gerasimos; 05-06-2022 at 04:53 PM.

  28. #28
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    Quote Originally Posted by gerasimos View Post
    I addedd

    Please Login or Register  to view this content.
    in order to change font size (18 or 20) to all worksheet
    Instead of this loop, you can have the whole table at once:
    Please Login or Register  to view this content.

    Quote Originally Posted by gerasimos View Post
    Then I added
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    In order to make row height 32 to all worksheet.
    I counted 5 places in the code where the height of the rows should be constantly increased, including two loops, which would affect the macro execution time. I left them commented in the code below. It is better to increase the height of the rows once, but in the redundant range (Rows("15:10000") ). It will be faster.

    Quote Originally Posted by gerasimos View Post
    I also tried to make the cell E15 not be visible (and at every cell that has sheet name as yellow)
    Just format such cells with appropriate formatting code: ";;;"

    Please Login or Register  to view this content.
    Artik
    Last edited by Artik; 05-06-2022 at 07:35 PM.

  29. #29
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik This working PERFECT!

    Thank you for your advices, effort and help !

  30. #30
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44

    Re: How to force text to print at the bottom of the last page of an excel

    @Artik Hello again,

    I am coming back to this tread to ask sometinh else, that is not against forum rules.

    This code is working just fine, but suddenly it decided to print the invoice only rows A:B and only at MAC.

    What maybe wrong?

    If I reduce the size of C, D, E, F it works just fine ...

    Strange?

    Thank you !

  31. #31
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,476

    Re: How to force text to print at the bottom of the last page of an excel

    MAC is a foreign environment to me. I would even say that it is a hostile environment (I once tried to cooperate with it, but it got offended with me ). From the description of the problem, it seems that it is still offended.
    Sorry, I can't help you.

    Artik

  32. #32
    Registered User
    Join Date
    04-01-2022
    Location
    athens, greece
    MS-Off Ver
    office 365
    Posts
    44
    @Artik I fully understand what you are saying! An I dont like working with MAC but...
    Anyway, Thank you again for your help!

    Update! If someone playing with page margins, then everything (for MAC) crashes...
    Last edited by gerasimos; 05-16-2022 at 04:13 PM. Reason: Solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to force excel to print at the very bottom of the page
    By Curious Dude in forum Excel General
    Replies: 2
    Last Post: 02-25-2015, 01:56 PM
  2. Way to count rows in print out and force page break?
    By SDBoca in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2011, 12:12 PM
  3. Print rows at bottom of each page
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-30-2011, 04:39 PM
  4. [SOLVED] print bottom row repeat on every page
    By nilesh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  5. print bottom row repeat on every page
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 09:05 AM
  6. print bottom row repeat on every page
    By nilesh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] print bottom row repeat on every page
    By nilesh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1