Quote:
Originally Posted by Marcia
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
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
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.