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("Las t 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" wrote:

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. (Dont 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.


--
Regards,

OssieMac


"CL" wrote:

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" wrote:

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.

--
Regards,

OssieMac


"CL" wrote:

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("Las t 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