+ Reply to Thread
Results 1 to 38 of 38

Print footer directly after last row in page

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Print footer directly after last row in page

    Hello everyone

    I have a sheet that I made a code to print the sheet and the data in the sheet is dynamic by another code. I mean the number of rows that will be printed will not be fixed all the time
    Is there a way to print the footer directly after the last row of each page as the page sometimes has only 5 rows and at this case the footer is far away from the data .. so I need a way to make the footer near the data in that case? Is that possible?
    Note I don't want to include the footer as temporary row and delete that row after the print ..

    That's my try till now
    Please Login or Register  to view this content.
    Posted here too
    http://www.eileenslounge.com/viewtopic.php?f=30&t=31626
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Print footer directly after last row in page

    Hi Yasser

    Can you get the height of your normal page, check the height of each page to be printed and adjust the margin on the page in question accordingly?
    I am just thinking out loud now.
    Last edited by jolivanes; 01-16-2019 at 01:47 AM.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Thanks a lot for reply
    The page size is A4 .. I don't know what is the height of normal page .. Will I do that manually or there a way to do so?

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Print footer directly after last row in page

    Please Login or Register  to view this content.
    With the above, a times the height of a row would give you the height of a print page.
    Now I don't know if you need to convert from points to twerps or pixels or whatever.
    You'll need to know how many Horizontal Page Breaks you have so for the last page you can multiply the amount of rows in that page times the regular Row Height.
    The difference between the first found number (first page heigth) and the last page height needs to be added to the normal margin setting.
    But I leave all of this up to you because the way to go would be like Hans suggested in your other post.
    A lot of If.... Then coming after this by the looks of it.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    I have added this line to your lines to get the number of rows for the blank part of the last page
    Please Login or Register  to view this content.
    But I don't know how to set the margin relying on this information to be after the last row
    Hope to find suitable solution

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Hi Yasser

    Give this a go...Perhaps someone else can simplify...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 01-16-2019 at 12:01 PM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Thanks a lot my friend for great effort. It worked fine
    Can you guide me with the fixed numbers used in the code such as 11.69 and 15 / 72?

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    I have reset all the page breaks and rerun the code but as for the first two pages the footer is incorrect
    I would like to deal with only the last page as for adjusting the margins. So is it possible to adapt your code so as to deal with only the last page ??

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Hi Yasser

    This was quite a complex scenario...
    The 11.69 is the std size in inches of a A4 page
    15 is the sheets row height - my uploaded files rows were 15 points
    72 is the value of points per inch i.e if row height was 72points then it would be 1 inch wide...

    I have reset all the page breaks and rerun the code but as for the first two pages the footer is incorrect
    Perhaps we should insert code to set page breaks prior?
    Upload a sample of file in question and I'll have a look...
    Last edited by sintek; 01-17-2019 at 05:44 AM.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    The problem is that the number of pages in my file is not fixed as it is related to grabbing data from another sheet and the data grabbed is different .. so I posted this thread so as to deal with only the last page of the pages that will be printed ..
    As for the sample file the first one is OK but I will reset the page breaks each time I ran the original code

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    I manually reset and all works fine...Yes perhaps just insert code to reset prior to running...

    so I posted this thread so as to deal with only the last page
    Is there a way to print the footer directly after the last row of each page

    Inserting these snippets should handle any scenario....

    Just after

    Please Login or Register  to view this content.
    insert
    Please Login or Register  to view this content.
    Just before
    Please Login or Register  to view this content.
    insert
    Please Login or Register  to view this content.
    Last edited by sintek; 01-17-2019 at 05:56 AM.

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    But as for me when resetting the page breaks it works fine for the last page only and not as for the first two pages

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    This full code works for any of my scenario's...
    Perhaps you should upload a sample so that we can have a look...
    Please Login or Register  to view this content.
    Last edited by sintek; 01-17-2019 at 06:58 AM.

  14. #14
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    I got an error at this line
    Please Login or Register  to view this content.
    Subscript out of range .. I have put the last code in the first attachment

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    see amended code in Post13

  16. #16
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Thanks a lot for your great support
    The same problem with the first two pages as I got the footer inside the data and I tried to play around the numbers you referred to but the same problem
    I have commented out the line of the footer
    Please Login or Register  to view this content.
    As the footer will be put manually not by the code

    Can you help me fixing the code so as to deal with only the last page?!! as in my first try as this will be easier to modify the original code
    The only problem will be with the last page and how to suit the margin to this last page only
    Thanks a lot for great help

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Sorry Yasser...Would not know where to start lol...

    This is your file from post 1...Run the code and try and explain what is wrong with the outcome...All seems to work fine...Or...Am I missing something...
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    That's what I got for the first two pages
    Untitled.png
    And that was I talking about

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Are we running the same file...This is what I get...


    Page1.pngPage2.pngPage3.png


    EDIT....

    Is your printer settings set up for A4
    Last edited by sintek; 01-17-2019 at 08:55 AM.

  20. #20
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Yes the settings is set to A4 ....

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    My apologies Yasser...I cannot recreate your output...Works for me...has to do with your printer settings...Hope you get it sorted...Tx for rep +

  22. #22
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    You're welcome my friend. Thanks a lot for your awesome efforts and I hope other expert members to contribute to solve that

  23. #23
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Can't offhand think what it is...Can you perhaps give a screenshot of your printer settings...

    Are my results in Post 19 your expected outcome???

  24. #24
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    I have another idea .. How to deal with only the last page so I would remove the margin for this last page and put the footer manually but I want to make it as exact as possible as the original footer
    Please Login or Register  to view this content.

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Hi Yasser

    Any response on Post 23...
    Last edited by sintek; 01-17-2019 at 11:51 AM.

  26. #26
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    As for the printer settings: I don't know what do you mean exactly? I am dealing with more than a printer so it will be more convenient to make the code flexible and away from the printer settings (if possible)

  27. #27
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Are my results in Post 19 your expected outcome???
    Did you see the results in post 19...

    What is your actual scenario...Perhaps explaining the process could assist...Why not take control of everything from the the beginning...
    Make use of the code to set the page breaks i.e every 40 | 50 rows then print the range of each page break setting the footer at the end of the range...

  28. #28
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    The issue can be solved be inserting row at the end of each block as you said .. But I want it by changing the position of the margin (I thought it is possible)
    But as no one could solve it so it will not be useful to insist on trying to do it

    Thanks a lot for great help in this topic my friend

  29. #29
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Yasser...You still have not answered my question...

    Are my results in Post 19 your expected outcome???
    Did you see the results in post 19...

    If those are the results that you expect then the code works but will have to see why it does not work for you!!!


    But I want it by changing the position of the margin
    What margin...the Footer margin Top margin...Bottom margin...?

  30. #30
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Yes the results are correct but not as for me when applying the code
    The margin I would like to change is the bottom margin only

  31. #31
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Still don't get it...If we are running the exact same file with the exact same code...
    HOW ARE WE GETTING DIFFERENT RESULTS?

    Is there anything we are not taking into account...

  32. #32
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    I don't know why we got different results ..
    May be we have to deal with the same file so please attach the last working file for you and I will test it and give you the snapshot of my results

  33. #33
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    May be we have to deal with the same file
    Have you not being doing that...File from post 17

  34. #34
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Thanks a lot for continuous help
    Here's the file you have attached and when I run the macro this what I got as I have posted before
    Untitled.png

    I have attached the file again so as to easily follow the topic
    Attached Files Attached Files

  35. #35
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Print footer directly after last row in page

    Just don't get it...Works for me...Perhaps you should start another thread inquiring what the reason could be for different results...

    Post file and the two outputs ... linking to this one...

  36. #36
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    No problem my friend
    I will try to change my attitude and insert a row for my footer at the end of the data ... that's my only way now
    Thanks a lot for great help. I appreciate a lot your awesome help
    Regards

  37. #37
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 365 Apps for enterprise
    Posts
    147

    Re: Print footer directly after last row in page

    Good afternoon YasserKhalil

    I just saw this post and had a look on my computer office 2013
    I'm using your sample file

    Check your Margins are set to 0.75 for footer the page and the macro top right has to be click which will add the footer to the print
    to get your screen cap to match my computer i set the Margins to 0 and it looked the same

  38. #38
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Print footer directly after last row in page

    Thanks a lot for reply. I have played around the numbers till I got the desired output

+ 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. VBA to insert page total (print area) in footer with cell-like formatting
    By leb123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2017, 04:31 PM
  2. Replies: 1
    Last Post: 06-20-2014, 02:42 PM
  3. print footer on last page only
    By nlinetraffic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-20-2012, 06:51 PM
  4. [SOLVED] print footer on only one page in excel
    By Dan Connors in forum Excel General
    Replies: 1
    Last Post: 08-03-2005, 05:05 PM
  5. Replies: 5
    Last Post: 07-22-2005, 12:05 PM
  6. Print page with cell as footer reference
    By Hellboy in forum Excel General
    Replies: 3
    Last Post: 05-10-2005, 11:35 AM
  7. [SOLVED] How do I get the same header/footer to print on each page of a wo.
    By Stephen Connolly in forum Excel General
    Replies: 2
    Last Post: 03-17-2005, 08:06 AM

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