View Single Post
  #4   Report Post  
Dave O
 
Posts: n/a
Default Can all the sheets in one workbook all the same?

I've written some code that reads the page setup of the current
worksheet and applies those settings to all the tabs in the workbook.
If you try this code, run it on a Backup<< file first to make sure it
doesn't destroy your data. The caveat is: I have tested this code on a
few but not all possible combinations of print setups. It worked fine
for the combinations I tested but may choke on your particular set of
combinations. If it does choke, the code will generate an error and
stop; I would appreciate it if you'd contact me with problems that you
encounter so I can update and improve the code.

Sub CopyPrintFormats()
'declare booleans
Dim dBlackAndWhite As Boolean, dCenterHorizontally As Boolean, _
dCenterVertically As Boolean, dDraft As Boolean, dPrintGridlines As
Boolean, _
dPrintHeadings As Boolean, MasterZoom As Boolean

'declare longs
Dim dZoom As Long, dFitToPagesWide As Long, dFitToPagesTall As Long

'declare strings
Dim dCenterFooter As String, dCenterHeader As String, dLeftFooter As
String, _
dLeftHeader As String, dPrintArea As String, dPrintTitleColumns As
String, _
dPrintTitleRows As String, dRightFooter As String, dRightHeader As
String

'declare variants
Dim dBottomMargin As Variant, dFirstPageNumber As Variant,
dFooterMargin As Variant, _
dHeaderMargin As Variant, dLeftMargin As Variant, dOrder As Variant, _
dOrientation As Variant, dPaperSize As Variant, dPrintComments As
Variant, _
dPrintErrors As Variant, dRightMargin As Variant, dTopMargin As
Variant, dPrintQuality As Variant

With ActiveSheet.PageSetup
dBlackAndWhite = .BlackAndWhite
dCenterHorizontally = .CenterHorizontally
dCenterVertically = .CenterVertically
dDraft = .Draft
dPrintGridlines = .PrintGridlines
dPrintHeadings = .PrintHeadings
dPrintQuality = .PrintQuality
dFitToPagesTall = .FitToPagesTall
dFitToPagesWide = .FitToPagesWide
If .Zoom Then
dZoom = .Zoom
MasterZoom = True
End If
dCenterFooter = .CenterFooter
dCenterHeader = .CenterHeader
dLeftFooter = .LeftFooter
dLeftHeader = .LeftHeader
dPrintArea = .PrintArea
dPrintTitleColumns = .PrintTitleColumns
dPrintTitleRows = .PrintTitleRows
dRightFooter = .RightFooter
dRightHeader = .RightHeader
dBottomMargin = .BottomMargin
dFirstPageNumber = .FirstPageNumber
dFooterMargin = .FooterMargin
dHeaderMargin = .HeaderMargin
dLeftMargin = .LeftMargin
dOrder = .Order
dOrientation = .Orientation
dPaperSize = .PaperSize
dPrintComments = .PrintComments
dPrintErrors = .PrintErrors
dRightMargin = .RightMargin
dTopMargin = .TopMargin
End With

Dim SName As Variant
For Each SName In Sheets
Sheets(SName.Name).Select
With ActiveSheet.PageSetup
.BlackAndWhite = dBlackAndWhite
.CenterHorizontally = dCenterHorizontally
.CenterVertically = dCenterVertically
.Draft = dDraft
.PrintGridlines = dPrintGridlines
.PrintHeadings = dPrintHeadings
.PrintQuality = dPrintQuality
.FitToPagesTall = dFitToPagesTall
.FitToPagesWide = dFitToPagesWide
If MasterZoom Then
.Zoom = dZoom
Else
.Zoom = False
End If
.CenterFooter = dCenterFooter
.CenterHeader = dCenterHeader
.LeftFooter = dLeftFooter
.LeftHeader = dLeftHeader
.PrintArea = dPrintArea
.PrintTitleColumns = dPrintTitleColumns
.PrintTitleRows = dPrintTitleRows
.RightFooter = dRightFooter
.RightHeader = dRightHeader
.BottomMargin = dBottomMargin
.FirstPageNumber = dFirstPageNumber
.FooterMargin = dFooterMargin
.HeaderMargin = dHeaderMargin
.LeftMargin = dLeftMargin
.Order = dOrder
.Orientation = dOrientation
.PaperSize = dPaperSize
.PrintComments = dPrintComments
.PrintErrors = dPrintErrors
.RightMargin = dRightMargin
.TopMargin = dTopMargin
End With
Next SName
End Sub