MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Page Formatting

Posted by Gene Johnston on August 02, 2001 7:44 AM

I am trying to format multiple worksheets at tghe same time. I set up an array to select all of the worksheets to format and them then ActiveSheet.PageSetup to format all pages the same.
Can you tell me why this is not working?
Also is there a faster way to do pages setup. It seems like each property takes 30-60 seconds to be set.


ReDim Preserve sheetList(Sheets.Count - 1)
For i = 1 To (Sheets.Count)
sheetList(i - 1) = i

MsgBox "about to Start Page Setup"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 10
End With

Posted by Joe Was on August 02, 2001 10:28 AM

First format each Worksheet with the page setup menu select all your options then save. Then use this code:

Sheets("Related Form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Input Form").Select

Excel will remember all the sheet setups and run them directly from the "PrintOut" method which is the fastest way to do this. If you must use your code turn off the screen updating which will speed up the code when run. Use:

Application.ScreenUpdating = False

Your code here:

Application.ScreenUpdating = True

This sets screen updating back on after your code. JSW