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)

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)

One way is to use the Excel4 print macro (reearch this in Google).

Another is to trim the page setup code to include only the pertinent line.

Another is to start the code with:
Application.ScreenUpdating = False
and reset it to True at the end of the code.

--
steveB

Remove "AYN" from email to respond
"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)

If these don't work you might need to clean all unnecessary .emf files from
your temp folder (this slows down print preview, print
settings,saving,loading, etc....)

--
When you lose your mind, you free your life.


"STEVE BELL" wrote:

One way is to use the Excel4 print macro (reearch this in Google).

Another is to trim the page setup code to include only the pertinent line.

Another is to start the code with:
Application.ScreenUpdating = False
and reset it to True at the end of the code.

--
steveB

Remove "AYN" from email to respond
"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)

Perfect! Works in about than 30 seconds. Thanks!


"ben" wrote:

If these don't work you might need to clean all unnecessary .emf files from
your temp folder (this slows down print preview, print
settings,saving,loading, etc....)

--
When you lose your mind, you free your life.


"STEVE BELL" wrote:

One way is to use the Excel4 print macro (reearch this in Google).

Another is to trim the page setup code to include only the pertinent line.

Another is to start the code with:
Application.ScreenUpdating = False
and reset it to True at the end of the code.

--
steveB

Remove "AYN" from email to respond
"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)