Formatting all worksheets to fit on one page each

[Lazer]

I've tried to record a macro to select all worksheets in the activ
workbook and format them each to fit on 1 landscape page. When I ru
the macro on a workbook that has the worksheets with the names liste
in the macro, below, they still hang off the page a little. In othe
words, this macro doesn't seem to work.

1) What do I need to do to get this macro working?
2) What is the shorthand syntax to select all sheets in the activ
workbook? I'm looking for something like: "Sheets(*).select" o
something...

Thanks!
Eliezer


Code
-------------------

Sub formatToPrint()

Sheets(Array("Cover page", "Berrios, Milton", "Burroughs, Carrie", _
"Campbell, David", "Clark, Jeff", "Craig, Tammy", "Crawford, Tara", "Eakin, Maki", _
"Eubanks, Shad", "Good, Nate", "Hall, Matthew", "Kehm, Jaime", "Locklear, Chad", _
"Paul, Leena", "Rampey, Chris", "Rootlieb, Tyler", "Stevens, Keven", _
"Taylor, Kenneth", "Tremblay, Lisa", "Turtletaub, Jake", "Williams, Jeff", _
"Wuest, Kevin")).Select
Sheets("Rootlieb, Tyler").Activate
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.35)
.RightMargin = Application.InchesToPoints(0.35)
.TopMargin = Application.InchesToPoints(0.35)
.BottomMargin = Application.InchesToPoints(0.35)
.HeaderMargin = Application.InchesToPoints(0.35)
.FooterMargin = Application.InchesToPoints(0.35)
.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 = 1
End With
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover page").Select

End Su

 

[Tom Ogilvy]

Sub formatToPrint()

For each sh in Worksheets
With sh.PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover page").Select
End Sub


Don't perform settings you don't need - each setting is done as a separate
call to pagesetup which is very slow.

VBA has very little support for grouped sheets. You need to loop through
the sheets.

This is a "trick" approach posted by KeepitCool

Sub formatToPrint()

Sheets.Select
Sheets("Rootlieb, Tyler").Activate
With ActiveSheet.PageSetup
' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.35)
' .RightMargin = Application.InchesToPoints(0.35)
' .TopMargin = Application.InchesToPoints(0.35)
' .BottomMargin = Application.InchesToPoints(0.35)
' .HeaderMargin = Application.InchesToPoints(0.35)
' .FooterMargin = Application.InchesToPoints(0.35)
' .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 = 1
End With
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover page").Select

End Sub

--
Regards,
Tom Ogilvy

 

[Stephen Rasey]

Sheets(Array("Sheet1","Sheet2","Sheet3")).select

I was able to select a group of sheets, the do a Page Setup on the group.
They come out at different scales, but all as one page per worksheet.

Stephen Rasey
Houston
http://excelsig.org

 

[Lazer]

Tom, thank you so much - great trick! :-