Page setup functionallity it disturbingly slow via VBA. It is distinctly
faster via XL4 macros. Search this forum or google for XL4 macros and you
might find something to head you in the right direction...
--
HTH...

Jim Thomlinson


" wrote:

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