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.
Sheets(1).Activate
ReDim Preserve sheetList(Sheets.Count - 1)
For i = 1 To (Sheets.Count)
sheetList(i - 1) = i
Next
Sheets(sheetList).Select
Sheets(1).Activate
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
Range("A2").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
