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?

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?

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?

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?

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.