Slooooow PageSetup
If you recorded your macro, then remove all the settings that don't change.
Only set what must be set.
If it is still slow, you might use the excel4 macro equivalent:
From: John Green )
Subject: Pagesetup code takes too long
Newsgroups: microsoft.public.excel.programming
View complete thread (5 articles)
Date: 1999/03/29
Macro =
"Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1, {1,1},,,,,.25,.25)"
ExecuteExcel4Macro Macro
HTH,
John Green - Excel MVP
Sydney
Australia
=================================
From: John Green )
Subject: About PageSetup..
Newsgroups: microsoft.public.excel.programming
View complete thread (10 articles)
Date: 2001-01-22 12:57:23 PST
PageSetup in VBA has always been a painfully slow process. If you can't
avoid having
to set these parameters, you can use the Excel 4 macro function, PAGE.SETUP
to carry
out most of the PageSetup operations much more quickly. The following two
macros are
almost equivalent, and should give you the clues you need to start using
PAGE.SETUP.
You can download a full description of all the Excel 4 macro functions from
Microsoft's web site:
Sub PS()
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
.LeftHeader = "My Company"
.CenterHeader = ""
.RightHeader = "&D / &T"
.LeftFooter = "Highly Confidential and Proprietary"
.CenterFooter = ""
.RightFooter = "Finance"
.LeftMargin = Application.InchesToPoints(0.54)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.36)
.HeaderMargin = Application.InchesToPoints(0.22)
.FooterMargin = Application.InchesToPoints(0.17)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 600 ' does not work with all the printers
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub
Sub PS4()
head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
pLeft = 0.54
pRight = 0.3
Top = 0.4
bot = 0.36
head_margin = 0.22
foot_margin = 0.17
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," & pRight &
","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," & h_cntr
& ","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," & pscale
& ","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," & quality
& ","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes & "," &
Draft & ")"
Application.ExecuteExcel4Macro pSetUp
End Sub
John Green (Excel MVP)
Sydney
Australia
=====================
If all the sheets will have the same setting, you might use this trick
posted by KeepItCool:
Sheets(Array("Sheet1", "Sheet3")).Select
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show
keepITcool
The first sheet selected should have the settings you want already set.
--
Regards,
Tom Ogilvy
"Dcolecpa" wrote in message
...
I am programatically setting my print range, footers, etc on a 19 sheet
Workbook. I takes ~ a minute per sheet for the macro to run. Is there a
way
to make this faster?? (I've got a new Dell running 2.8 mghz with 512 mg
or
Ram runnning office 2000 on Windows 2000 professional)
|