Finding ways to identify last row of every printed page

[CL]

Hi all,

I had pre-determined the number of rows for the quotation table for each and
every print page so that the page print will be constant

However, I would like to state that my problem is that whenever I expand any
row height within the table, there is a high chance that the number of rows
of table in each print page will also change.

And using my method to pre-determine the number of rows for the quotation
table, the macro will put a bordering at the wrong row (if I have change to
height of some rows within the same page, thus forcing previous rows at the
end to go to next print page)

I am also getting VBA to generate a print preview immediately after hitting
the "Convert to Quotation Form" button, so you will see that all formating
and resizing has been fixed to standardize the quotation print output.

Please help and advice me on how to get VBA to recognize the last row count
for each print page to be dynamic, instead of my current hard-coded counting
of rows for each printed page.

I am currently using these code for hard-coding:-

With destSh.PageSetup
.PrintArea = "$A1:$H" & lastprintable
.PrintTitleRows = "$1:$21"
.Zoom = 44
' .FitToPagesWide = 1
' .FitToPagesTall = 8
.PrintErrors = xlPrintErrorsDisplayed
' .RightFooter = "&8Printed on : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Print
Date"), _
"yyyy-mmm-dd hh:mm:ss")
.CenterFooter = "Page &P of &N"
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
End With
'for automatic border underline
ub = 109
For fndline = ub To lastprintable
If lastprintable > ub Then
destSh.Range(Cells(ub, 1), Cells(ub, 8)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

ub = ub + 90 ' how to change 90 to be dynamic??
End If
Next fndline

pages = ExecuteExcel4Macro("Get.Document(50)")
destSh.Range("D12").Value = "Pages (Incl this page) : "


Thank you once again.

Cheers,
CL

 

[OssieMac]

Hi,

The code you posted suggests you can hadle the required code if given some
pointers in the right direction. Please understand that the following are
suggestions and are untested.

Try inserting Workbook_BeforePrint event in Thisworkbook module.

Set the first line to
Application.EnableEvents = False
Set Cancel = True 'To cancel the original print call and

Then setup a loop for printing for one page at a time and set the print area
for each page individually and print each individually with the following
code included.
..FitToPagesWide = 1
..FitToPagesTall = 1
..FirstPageNumber = lngPageCounter 'Set up a variable for the page numbers

Ensure that you insert the following line at the end of the code
Application.EnableEvents = True

Create a sub that you can run on its own to reset events to true if the code
fails prior to reaching the line to turn events back on otherwise all future
events will fail to execute.

 

[CL]

Hi OssieMac,

Your suggestion is feasible but in my case, the loop I am trying to do will
determine the end of the quotation table (followed by other text below the
table such as signature, name etc.) So to say that the table will start
about row 21 of page 1 and end at any page. For pages not the lasty page, it
is easy to use your suggestion but for the last page, it will not be so easy.


I have a small attachment but dun know how to post it here. With the
attachment, it will be easier to discuss.

Cheers,
CL

 

[OssieMac]

Hi again CL,

If you can give me a bit more information then perhaps between us we can get
on top of this.

Firstly lets see if I understand your problem correctly so please confirm or
otherwise the following.

You have a fixed area for each sheet in the quotation and want that fixed
area to always print out on the required sheet number irrespective of changes
to the row width.

If the above assumption is correct:
How many sheets are there and is it a fixed number or is it variable.
How many rows are on each sheet. If different for each sheet then list the
number of rows on each sheet including the last sheet.
What row numbers are required on each sheet.
How many columns wide are the sheets for the print out. (Don’t care about
actual width of each column; just how many columns)
What format is required for sheet numbering in the header or footer. (ie.
Page x of y) and what position is it required; Left, Centre of Right.

 

[CL]

Hi OssieMac,

Thanks for following up.

Here goes...

1) I have a costing sheet and by hitting the macro to fire, it will add a
new worksheet and named it "Quotation Sheet".

2) And in this "Quotation Sheet", I have a macro for page setup as follows
(so that all users will have the same print size, irregardless of pages).

macro for the page setup:-
With destSh.PageSetup
.PrintArea = "$A1:$H" & lastprintable
.PrintTitleRows = "$1:$21"
.Zoom = 44
.FitToPagesWide = 1
.FitToPagesTall = 8
.PrintErrors = xlPrintErrorsDisplayed
.RightFooter = "&8Printed on : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Print
Date"), _
"yyyy-mmm-dd hh:mm:ss")
.CenterFooter = "Page &P of &N"
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
End With


3) Next, I will then have another macro to determine which row is the last
row for each page. As this is a quotation, there will be information such as
name, address, tel etc. on the top of first page, follow by the table of the
quotation. And lastly , the ending of the document will be the name of the
seller, area for signature, terms and conditions of quotation etc.

In another words, the table (for the quotation or list of items quoted) is
the problem area. Initially, I am using "hard-code" and loop to ensure that
on the ending of the table of each and every page, "line" is created as a
border. However, I discovered that when changes are made to the height of
any rows, the pagination will either increase or decrease. Thus, it also
causes the "hard-coded" "line" border creation to be created not at the last
row of the table (but in between the table on each page).

I have also been exploring Hpagebreak to control this but somehow, when the
page setup macro is added, it will not start the "line" creation macro -
"Hpbrcnt" is always equal to 0. But if the page setup macro is not added,
the count of page will not be aligned to the optimum printout size I would
like to standardize.

Here is the macro immediately after the page setup:-

Hpbrcnt = destSh.HPageBreaks.Count

For loopHpbrcnt = 1 To Hpbrcnt


ub = destSh.HPageBreaks(loopHpbrcnt).Location.Row - 1


If lastprintable > ub Then
destSh.Range(Cells(ub, 1), Cells(ub,
8)).Select


Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

End If

Next loopHpbrcnt


If you have a way that I can post an attachment, please tell me as it would
be easier for you to visualize.

Thanks again.

Cheers,
CL

 

[OssieMac]

On your question "If you have a way that I can post an attachment". I don’t
want to advertise my email address and that is the only way that I know of. I
agree that it is hard to really visualize all that you are trying to achieve.

Anyway, I have a macro that was written for someone else that has markers in
the worksheet for the Start row and End row of each page to be printed. The
markers are placed in a column to the right of the part to be printed. The
column can be hidden after entering the Start and End info for each page. For
now we will call the column Page size column.

For the macro to work, choose an empty column to the right of your data and
this can be the Page size column:
Select the cell in this column corresponding to the first row of first page
to be printed.
Enter Start 01.
Select the cell in this column corresponding to the last row of the first
page.
Enter End 01

Select the cell in this column corresponding to the first row of the second
page.
Enter Start 02
Select the cell in this column corresponding to the last row of the second
page.
Enter End 02

Repeat the above for all pages to be printed and you now have a marker for
the top and bottom row of each print page.

You can ignore rows to be repeated on each page. Just ensure that they are
included in the code because they do not need to be included in the actual
print area because they get added to the print area.

Copy the macro into ThisWorkbook module.

When print is selected, the macro now selects each page to be printed
individually and sets it to print 1 page wide by 1 page high irrespective of
the number of rows or the height of the rows. You will see in the macro a
counter that looks after page number and the macro finds the last entry in
the Page size column for the total number of sheets.

Even if this is not exactly what you want then it might give you some
pointers as to how you might achieve your desired result. I have documented
it as much as possible so it might be easier to read if you actually copy it
into the VBA editor but do it on a copy of your workbook so you don’t ruin
any other good work if you should have a problem.


Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Prevent recursive calls to this event. _
Note: If code fails you must run the sub _
below to re-enable events
Application.EnableEvents = False

Dim lngPageCounter As Long 'Counter for page numbers
Dim lngLastPageNumber 'Last page number for footer/header
Dim rngStart As Range 'Used to find first row of each page
Dim rngEnd As Range 'Used to find last row of each page
Dim rngPageCol As Range 'Column that holds Start and End Id's of pages
Dim strPageToFind As String 'String for Find

'Cancels original print call so that following code is run in lieu.
Cancel = True

With Sheets("Sheet1")
'Edit both column J's to match your Page size column.
Set rngPageCol = .Range(.Cells(2, "J"), _
.Cells(.Rows.Count, "J").End(xlUp))

'Assign last page number to a variable _
Edit J to match your Page size column with Start/End
lngLastPageNumber = Val(Right(.Cells(.Rows.Count, "J") _
.End(xlUp), 2))
End With

lngPageCounter = 0

Do 'Start the loop
'Increment the page counter
lngPageCounter = lngPageCounter + 1
'Assign Start and page number to variable ready for Find
strPageToFind = "Start " & Format(lngPageCounter, "00")

'Find cell with Start of page and assign to a variable
Set rngStart = rngPageCol.Find(What:=strPageToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'Test if page start found. If not then finished.
If Not rngStart Is Nothing Then 'Found target
'Name the cell for start of print area
'If not column A then edit to your column.
ActiveWorkbook.Names.Add Name:="Start_Page", _
RefersToR1C1:=Cells(rngStart.Row, "A")
Else
MsgBox "Finished printing" 'For testing only. Can delete in real code
Exit Do 'Exit the loop because no more pages
End If

'Assign End and page number to variable ready for Find
strPageToFind = "End " & Format(lngPageCounter, "00")

'Find cell with End of page and assign to a variable
Set rngEnd = rngPageCol.Find(What:=strPageToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'Test for rngEnd found. If not found then error
'because End number is missing
If Not rngEnd Is Nothing Then 'Found target
'Name the cell for end of print area
'If not column I then edit to match your column.
ActiveWorkbook.Names.Add Name:="End_Page", _
RefersToR1C1:=Cells(rngEnd.Row, "I")
Else
MsgBox "Error. Did not find End of page to " & Chr(13) & _
"match Start of page number " & lngPageCounter
Exit Do 'Exit the loop because of error
End If

ActiveSheet.PageSetup.PrintArea = "Start_Page:End_Page"

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1" 'Delete line if NOT required
.RightFooter = "Page " & lngPageCounter & " of " _
& lngLastPageNumber
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintQuality = 600
.CenterHorizontally = True 'Set as required
.CenterVertically = False 'Set as required
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.FirstPageNumber = lngPageCounter
.Order = xlDownThenOver
.BlackAndWhite = True
.FitToPagesWide = 1 '{force printing
.FitToPagesTall = 1 '{to fit page
.PrintErrors = xlPrintErrorsDisplayed
End With

'Following line used during testing only
'ActiveWindow.SelectedSheets.PrintPreview

'Comment out the following line and uncomment line above _
if you want to use print preview during testing
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Loop While Not rngStart Is Nothing

'Re-enable events.
Application.EnableEvents = True

End Sub

Sub Reset_EnableEvents_True()
'Run if above event fails otherwise Events remain turned off
'Can be run from the VBA editor.
Application.EnableEvents = True
End Sub

 

[CL]

Hi OssieMac,

Thanks for replying but I had already all those start / end page info
(except that in between the table, it is so dynamic with each different users
that there are no chances to tag them before I know how they are filling in
to quotation.

However, I have also started this question in one of the forum and have
attachment there. If you do not mind, you may visit the forum as a guest and
view the attachment file to understand what I meant.

The thread is titled "Indicate the row number for each last row in each new
page printed" under user ID "clng". The forum address is www.excelforum.com,
under the "Excel Programming" forum.

Hope to hear from you with some advice if you dont mind visiting that forum.

Thanks again

Cheers,
CL

 

[CL]

Hi OssieMac,

I think I had managed to work around this by still using Hpagebreak
collection to identify ending rows of each print page. The earlier problem
is that after doing a page setup, the hpagebreak will not count the number of
printed pages. Hence, I added print preview to "activate" hpagebreak.

Haha, but now I am experiencing out of range subscript (error 9).

Cheers,
CL