Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting all worksheets to fit on one page each
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 ------------------- -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting all worksheets to fit on one page each
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 "Lazer " wrote in message ... I've tried to record a macro to select all worksheets in the active workbook and format them each to fit on 1 landscape page. When I run the macro on a workbook that has the worksheets with the names listed in the macro, below, they still hang off the page a little. In other 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 active workbook? I'm looking for something like: "Sheets(*).select" or 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 Sub -------------------- --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting all worksheets to fit on one page each
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 " wrote in message ... I've tried to record a macro to select all worksheets in the active workbook and format them each to fit on 1 landscape page. When I run the macro on a workbook that has the worksheets with the names listed in the macro, below, they still hang off the page a little. In other 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 active workbook? I'm looking for something like: "Sheets(*).select" or 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 Sub -------------------- --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting all worksheets to fit on one page each
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print several worksheets on one page | Excel Discussion (Misc queries) | |||
Add page numbers to multiple worksheets without changing page setu | Excel Discussion (Misc queries) | |||
Printing multiple page worksheets on 1 single page | Excel Discussion (Misc queries) | |||
Conditional Linking and Formatting across worksheets to form Summary Page - Only pull/link non-null fields | Excel Worksheet Functions | |||
How do I use 2 worksheets in 1 page | Excel Worksheet Functions |