Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-16-2022, 08:44 AM
Marcia's Avatar
Marcia Marcia is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
Bottom Line in every page
 
Join Date: May 2018
Location: Philippines
Posts: 497
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Bottom Line in every page

Hi. I found in another site a code that draws a bottom line at every page of a data sheet. I would prefer that the vba need not show a print preview and the bottom line should be drawn from col B to F.


Thank you for all the help, past and present.
Code:
Sub BottomLine()
Dim ws As Worksheet
Dim pb As HPageBreak
Dim LastRow As Long


For Each ws In ThisWorkbook.Worksheets
ActiveWindow.View = xlPageBreakPreview

For Each pb In ws.HPageBreaks
LastRow = pb.Location.Offset(-1, 0).Row

With ws.Rows(LastRow).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Next pb
Next ws
End Sub
Attached Files
File Type: xlsm Lines.xlsm (45.5 KB, 2 views)
Reply With Quote
  #2  
Old 08-16-2022, 01:14 PM
p45cal's Avatar
p45cal p45cal is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
 
Join Date: Apr 2014
Posts: 648
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Untested, try:
Code:
Sub BottomLine2()
Dim ws As Worksheet
Dim pb As HPageBreak
Dim LastRow As Long


For Each ws In ThisWorkbook.Worksheets
  origview = ActiveWindow.View
  ActiveWindow.View = xlPageBreakPreview

  For Each pb In ws.HPageBreaks
    LastRow = pb.Location.Offset(-1, 0).Row
    With ws.Range("B" & LastRow & ":H" & LastRow).Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThin
    End With
  Next pb
  ActiveWindow.View = origview
  'ws.DisplayPageBreaks = False
Next ws
End Sub
There's a commented out line you can add if you find you have unwanted pagebreaks showing.
Reply With Quote
  #3  
Old 08-18-2022, 08:26 AM
Marcia's Avatar
Marcia Marcia is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
Bottom Line in every page
 
Join Date: May 2018
Location: Philippines
Posts: 497
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you p45Cal.
There are issues when I pasted the code to the actual data sheet.
1. The code took several seconds to do its job and the sheet was "dancing" during execution. The actual sheet has several columns populated with formulae. Only columns B to H need to be printed in a report that is why I only showed cols B to H in the mock up data.
2. After running the code, the sheet (actual) ended in a page break view with a message, "subscript out of range." Though when I shifted the sheet to normal, the bottom lines were there.
3. When the page break has a row hidden below it, there was no line in the page break (row 121).
Attached Files
File Type: xlsm Lines.xlsm (46.5 KB, 1 views)
Reply With Quote
  #4  
Old 08-19-2022, 06:10 AM
p45cal's Avatar
p45cal p45cal is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
 
Join Date: Apr 2014
Posts: 648
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Quote:
Originally Posted by Marcia View Post
1. The code took several seconds to do its job and the sheet was "dancing" during execution. The actual sheet has several columns populated with formulae.
I've put Application.ScreenUpdating lines to try and remove that. If it's still a bit slow, come back.

Quote:
Originally Posted by Marcia View Post
2. After running the code, the sheet (actual) ended in a page break view with a message, "subscript out of range." Though when I shifted the sheet to normal, the bottom lines were there.
That hasn't happened here; what line did it report was at fault?
If it comes up with an error and you click End, it will not execute the rest of the code which would have put things as you want.

Quote:
Originally Posted by Marcia View Post
3. When the page break has a row hidden below it, there was no line in the page break (row 121).
This was the most difficult to solve and the code has been tweaked accordingly.
The reason no line was showing was that your code found the page break (which is the cell that is printed at the top of the next page) and added a bottom-of-cell line to the row above it. This is OK most of the time, until the row above the page break is also hidden, so any borders to that row aren't seen. I had to move back up, row by row, looking for the next visible row above the page break and added the line to that.


There are quite a lot of changes to the code:
Moved it to a standard code-module (having it in a sheet's code-module risks you losing it should you delete the sheet, and anyway, it makes no sense to have in a sheet's code module when it's working on multiple sheets).

Added a bit to embellish the Totals row.

For the code to work, the code has to switch the sheet it's working on to page break preview view, and for that to happen the sheet needs to be the active sheet. So while that happens, there's a danger that the sheet which was the active sheet before the macro runs is not the same as the one that is active after it has run. That has been addressed.
Attached Files
File Type: xlsm msofficeforums49513Lines.xlsm (49.4 KB, 1 views)
Reply With Quote
  #5  
Old 08-19-2022, 06:01 PM
Marcia's Avatar
Marcia Marcia is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
Bottom Line in every page
 
Join Date: May 2018
Location: Philippines
Posts: 497
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Many many thanks for going the extra mile p45Cal. We are nearly there.
I have attached a part of the actual workbook without the external links for better troubleshooting of some problems, like:
The workbook contains more than 20 sheets and the bottom line code should run on select/active sheets only. I tried removing the "next ws" line but it created a chain of errors
Attached Files
File Type: xlsm BottomLineMultiplePages.xlsm (113.4 KB, 1 views)

Last edited by Marcia; 08-19-2022 at 08:15 PM.
Reply With Quote
  #6  
Old 08-20-2022, 05:57 AM
p45cal's Avatar
p45cal p45cal is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
 
Join Date: Apr 2014
Posts: 648
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

See attached
Attached Files
File Type: xlsm msofficeforums49513BottomLineMultiplePages.xlsm (409.0 KB, 2 views)
Reply With Quote
  #7  
Old 08-20-2022, 08:34 AM
Marcia's Avatar
Marcia Marcia is offline Bottom Line in every page Windows 10 Bottom Line in every page Office 2019
Expert
Bottom Line in every page
 
Join Date: May 2018
Location: Philippines
Posts: 497
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

This is perfect p45Cal. I did some google reading to get an understanding on the code that you built.
This would save us from the tedium of erasing and adding page break lines whenever there is a row with new data from the previous month's records.
Thank you...
Generosity, kindness and patience is alive in this forum.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bottom Line in every page Table styles - First column shading with bottom line on page break NicB Word Tables 2 02-20-2019 03:05 AM
Paginate table so cell headings appear top of page instead bottom page when no room bottom page mtcn Word Tables 5 12-11-2014 12:49 PM
Bottom Line in every page get rid of line above page numbers at bottom of pages. Joanrichmond Word 1 02-11-2014 12:57 PM
Bottom Line in every page line and ?object at bottom of page in word 2007 doc btdaniel Word 10 07-23-2012 03:03 AM
Bottom Line in every page Horizontal line at bottom and top each page BrutalLogiC Word 1 07-03-2011 02:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft