#1
|
||||
|
||||
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 |
#2
|
||||
|
||||
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 |
#3
|
||||
|
||||
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). |
#4
|
||||
|
||||
Quote:
Quote:
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:
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. |
#5
|
||||
|
||||
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 Last edited by Marcia; 08-19-2022 at 08:15 PM. |
#6
|
||||
|
||||
See attached
|
#7
|
||||
|
||||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
get rid of line above page numbers at bottom of pages. | Joanrichmond | Word | 1 | 02-11-2014 12:57 PM |
line and ?object at bottom of page in word 2007 doc | btdaniel | Word | 10 | 07-23-2012 03:03 AM |
Horizontal line at bottom and top each page | BrutalLogiC | Word | 1 | 07-03-2011 02:22 AM |