You could add a couple of things to help speed up but not by much.

Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wSheet In Worksheets
yada yada yada
Next wSheet
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.Save

It has been my experience that page and print setups are very slow.

With 50 sheets a few minutes is to be expected.

Perhaps others have some ideas. He says hopefully<g


Gord

On Mon, 16 Mar 2009 15:11:02 -0700, Tondos
wrote:

Thanks, Gord.

I've had several attempts at creating this code, but I could only find this
one in my Personal workbook. I think this was one of my earlier, recorded
macros from 2 years ago.

Anyway, print range would definitely be unique to each sheet, but footer and
margins, etc. would be identical for all sheets.

The macro was supposed to save the time of selecting all the sheets and
needing to manually update the footer info. So the macro has saved the
'manual entry time' but caused a long 'waiting time' while the code executes!

Here is the code:

Sub FilePathFooterOrig()

'Formats Standardized Footer & Saves

Dim wSheet As Worksheet

For Each wSheet In Worksheets

With wSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&8& My Company Name " & Chr(10) & "&8&D &T" & Chr(10)
& Application.UserName
.CenterFooter = "&8&P/&N"
.RightFooter = "&8&Z" & Chr(10) & "&8&F" & Chr(10) & "&8&A"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Next wSheet

ActiveWorkbook.Save

End Sub

Any help is greatly appreciated!

Tondos

"Gord Dibben" wrote:

Any print or page setup on all sheets takes time depending upon the
complexity of the setup.

Remember............you're working with a printer driver.

I can't see your code but if all you need is common sheet print setups I
would suggest just grouping the sheets then adding the footer manually.

If each sheet needs its own print settings then you must use the macro to
just set the footer.

Post the code..............may be ways to speed it up.


Gord


On Mon, 16 Mar 2009 13:41:04 -0700, Tondos
wrote:

Gord,

I currently use a macro I wrote to apply a common footer to ALL worksheets
in the currently active workbook. It's a very basic For:Next code for each
WS. The problem is, it takes 5-6 seconds per worksheet to actually run the
code. So, my larger workbooks that have 50+ worksheets take several minutes
to run the footer code.

Is there a different code to select all the sheets in the active workbook,
apply the footer, and do so more quickly than my current? Also, I'm a novice
with VBA, but actively learning.

Thanks!

Tondos

"Gord Dibben" wrote:

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!