FitToPagesTall would normally be used to squeeze data that slightly
exceeded one page in length onto one page.
A TallPages calculation of 1000/40, if it worked, would result in
something unreadable.
Also, zoom and fit to pages are mutually exclusive - use one or the other.

So... try commenting out the TallPages, FitToPages and the Zoom lines.
Then add this line just below your variable declarations...
Application.DisplayPageBreaks = False.

Also, as Jim Thomlinson pointed out, using XL4 pagesetup code can be
faster. However, unless you are considerable smarter than average, it will take
more time than it is worth to master it. (i had lots of spare time)
Furthermore, I recall reading that there is a high probably that MS will "improve"
the next release of Excel by eliminating the use of XL4 code.
--
Jim Cone
Portland, Oregon USA





wrote in message
Hi all:

I'm using the following code to format the page setup for a workbook,
but as soon as I introduced it, the process time increased from 15
seconds to 3.5 minutes. Can anyone tell me why my code is so slow?

DestWS is the worksheet in the workbook this code is acting on. There
are 2 workbooks generated by my previous code and multiple worksheets
that are created per workbook that the following code is applied to.
DestEntRng is the entire range of data in DestWS, anywhere from 5 rows
to 1000, columns A:Z. BookType is a simple string for the footer
field.

Public Sub RptPageSetup(ByVal DestWS As Worksheet, DestEntRng As
Range, BookType As String)
Dim TallPages As Long

TallPages = Round(DestEntRng.Rows.Count / 40, 0)

With DestWS.PageSetup
.PrintTitleRows = "$1:$4"
.PrintArea = DestEntRng.Address
.LeftFooter = "Kidney Foundation of Canada"
.CenterFooter = "Page &P of &N"
.RightFooter = "Online " & BookType & " Report"
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = 56
.FitToPagesWide = False
.FitToPagesTall = TallPages
End With
End Sub