Hmmm... Excel OVERRIDES vpagebreak and hpagebreak vb code????

[wjoc1]

Hi,

I am dynamically creating a spreadsheet from contents within another
However sometimes the contents in some of the columns is very long
When this happens Excel inserts a vertical page break into th
spreadsheet automatically (I have none in by default; it's just on
page wide). I thought I could override this by placing the following i
my vb


Code
-------------------

Worksheets("Job card").VPageBreaks(1).Location = Worksheets("Job card").Range("M5"
-------------------


This is being ignored if the columns are too long and it just fires i
a page break whereever it feels appropriate.

Similarly I insert horizontal page breaks as you move down the shee
with vb also using the following line of code


Code
-------------------

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(intCurRow, 1)

-------------------


Where *intCurRow* is the current row number. Lovely works fine 99% o
the time however if the number of rows I need to include on one pag
exceeds a certain number (69 rows in my case) Excel overrides my v
code above and just inserts a page break itself.

Can someone please please shed some light on this for me, I presume i
has something to do with the ratio of height to width of informatio
being displayed??? But can I override it successfully in vb? At th
moment I have to go to page break preview and do this manually when
have finished creating the spreadsheet which is very annoying

This is my first post and it's gonna make or break this forum for m
so if anyone can solve this problem for me I'll be gratefull beyon
words.

Thanks,
Lia

 

[Tom Ogilvy]

Excel will insert a pagebreak where it must to print the page in accordance
with the print parameters you have specified. If the page will only hold 69
rows, then if you haven't put in a pagebreak before the 69th row, excel will
put in an automatic pagebreak. A manual pagebreak can be less than the
maximum distance, but if it is more than the maximum distance, an automatic
pagebreak must exist.

You can use fit to print and specify the number of pages wide and number of
pages long or leave one of those blank and it will override all pagebreaks.
So perhaps this is the answer for you.