how can I count the vertical pages of a print job?

[Guest]

I am trying to write some code where I am trying to stay within the one page
width by however many pages long. I don't like Excel's automatic page set-up
because sometimes it doesn't maximize the printed page space so I have done
the following ...

i = 10
ActiveSheet.DisplayAutomaticPageBreaks = True
ActiveSheet.PageSetup.Zoom = i
'keep going up til 1 then drop back down one
Do Until ActiveSheet.VPageBreaks.Count = 1
i = i + 1
ActiveSheet.PageSetup.Zoom = i
Loop
i = i - 1
ActiveSheet.DisplayAutomaticPageBreaks = False

Do you have any ideas as to why my code doesn't work? It appears I am not
using the vpagebreaks properly ... any ideas .... ?

Thank you !!

 

[Ron de Bruin]

Hi Rubble

I am trying to write some code where I am trying to stay within the one page
width by however many pages long.

Maybe this will help

Maybe change this line ?
.Orientation = xlLandscape

Sub tester()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlLandscape
End With
Next sh
End Sub

 

[Guest]

Hi Ron --

Thank you for your reply -- I am using landscape for my page setup; however,
even with the code you provided it is not working properly -- my sheet goes
down to 45% zoom on my test file even though I can go up to 57% without any
vertical page-breaks.

Do you know if I am even trying the correct type of coding when I do the
VPageBreaks.Count? It acts pretty unstable when I watch the
VPageBreaks.Count when I step through the code.

Thanks Again ...

 

[Ron de Bruin]

Here is code that I use for the HPageBreaks
http://www.rondebruin.nl/hpagebreaks.htm

Maybe you can use some code of it

 

[Guest]

Thank you !!! The problem I was having with the vpagebreak was that it
didn't "refresh" the count when I changed the zoom properties -- so after
looking at the code you sent over and after going back over your original
comment about running pagesetup.xlLandscape I decided that when I changed the
zoom I would also tell it to make sure it was landscape and apparently that
is enough to cause the vpagebreaks.count to refresh.

So, it now works !!

Thank you for your responses !!!

Jim