Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting sheets in a workbook
If I have a workbook with several sheets for instance months, how can I get
them all set up the same way, i.e. margins, page size, fonts,etc. I have grouped the pages together but when grouped in page break view I cannot change the margins, I have to ungroup and change each sheet, any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting sheets in a workbook
Page Set up is not available when you group the worksheets, but a subroutine
could do it for you, However, it is a little slow. Anyway change the range and other parameters to suit your needs. Sub PrintFmt() Dim wks As Worksheet Application.ScreenUpdating = False For Each wks In Worksheets wks.Activate ActiveSheet.PageSetup.PrintArea = "$A$1:$I$24" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next Application.ScreenUpdating = True End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "SM_NCSW" wrote: If I have a workbook with several sheets for instance months, how can I get them all set up the same way, i.e. margins, page size, fonts,etc. I have grouped the pages together but when grouped in page break view I cannot change the margins, I have to ungroup and change each sheet, any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting sheets in a workbook
I'm sorry I really don't know where all of this data would entered-- All I
really would like to do is set up a monthly form on individual worksheets. "Michael" wrote: Page Set up is not available when you group the worksheets, but a subroutine could do it for you, However, it is a little slow. Anyway change the range and other parameters to suit your needs. Sub PrintFmt() Dim wks As Worksheet Application.ScreenUpdating = False For Each wks In Worksheets wks.Activate ActiveSheet.PageSetup.PrintArea = "$A$1:$I$24" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next Application.ScreenUpdating = True End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "SM_NCSW" wrote: If I have a workbook with several sheets for instance months, how can I get them all set up the same way, i.e. margins, page size, fonts,etc. I have grouped the pages together but when grouped in page break view I cannot change the margins, I have to ungroup and change each sheet, any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting sheets in a workbook
For information on installing code see:
Getting Started with Macros and User Defined Functions http://www.mvps.org/dmcritchie/excel/getstarted.htm You have 2 choices - do it manually, sheet by sheet, as you are now, or utilize VBA code (macros) to do each sheet for you. The advantage to the latter is that you can be sure that each sheet is set up in identical fashion - that is, no user errors "SM_NCSW" wrote: I'm sorry I really don't know where all of this data would entered-- All I really would like to do is set up a monthly form on individual worksheets. "Michael" wrote: Page Set up is not available when you group the worksheets, but a subroutine could do it for you, However, it is a little slow. Anyway change the range and other parameters to suit your needs. Sub PrintFmt() Dim wks As Worksheet Application.ScreenUpdating = False For Each wks In Worksheets wks.Activate ActiveSheet.PageSetup.PrintArea = "$A$1:$I$24" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next Application.ScreenUpdating = True End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "SM_NCSW" wrote: If I have a workbook with several sheets for instance months, how can I get them all set up the same way, i.e. margins, page size, fonts,etc. I have grouped the pages together but when grouped in page break view I cannot change the margins, I have to ungroup and change each sheet, any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting sheets in a workbook
On Nov 6, 2:56*pm, Duke Carey
wrote: For information on installing code see: Getting Started with Macros and User Defined Functionshttp://www.mvps.org/dmcritchie/excel/getstarted.htm You have 2 choices - do it manually, sheet by sheet, as you are now, or utilize VBA code (macros) to do each sheet for you. *The advantage to the latter is that you can be sure that each sheet is set up in identical fashion - that is, no user errors If it is exactly the same format desired on each sheet, manual is pretty easy, and shouldn't be error-prone. 1) Format first sheet how desired. 2) Select all. (I like using the button for this, directly to the left of the column A button) 3) Click Format Painter button on the toolbar. 4) Select 2nd sheet tab with mouse. 5) Holding Shift, select last sheet tab with mouse. 6) Click on cell A1. If you can't see A1, make sure you scroll with the mouse (not any keyboard keys) to make it visible, then click on it. Now, that didn't address Page Setup, but the original question confused cell format with page setup. Those can't be done together. IE: margins are in Page Setup, Font Sizes are in cell formatting. I have a different trick for that, assuming you can use 2 hands at once. 1) On first sheet, open Page Setup and set appropriately. 2) Press CTRL-PgDn. (don't let go of CTRL) 3) Press CTRL-Y. (don't let go of CTRL) 4) Repeat #2-3 til bored, or til you reach the last sheet. Probably faster than running a macro, actually. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
add new sheets in a workbook with new sheets being a variable | Excel Discussion (Misc queries) | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How do I copy print formatting to multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
Formatting all sheets in a workbook to print the same | Excel Discussion (Misc queries) |