How do I copy a worksheet's print parameters to other worksheets?

R

Redundant908

I continually am changing the print parameters on worksheets. Often the print
parameters are the same throughout the spreadsheet. Certainly there must be a
way to: change the margins, have the same footer, always print landscape on
legal paper without changing this on every worksheet!
Thanks
 
J

John Michl

Try recording a macro that changes one print setting for the sheet with
the proper settings. Be sure to assign it a shortcut key. This will
actually record most if not all of the settings. The macro will be
recorded referencing the "ActiveSheet" not the actual sheet name. The
code below was captured that way. The short cut was set to Ctrl-Q. All
I set during the macro was the orientation. Now switch to any other
sheet that should be changed and press ctrl-q.

You could also add a For Next loop so that it cycles through all of the
sheets if you know a little VBA.

- John
www.JohnMichl.com

Sub SetPrintSettings()
'
' SetPrintSettings Macro
' Macro recorded 12/6/2005 by John '
' Keyboard Shortcut: Ctrl+q
'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
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 = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
 
G

Gord Dibben

Most print setup functions are available with grouped worksheets.

Right-click on first sheet and "select all sheets".

Do your print setup on the active sheet and will be done to all.

Setting the Print Area and Print Titles functions are not available with
grouped worksheets.


Gord Dibben Excel MVP
 
Top