Report of Margins and Worksheet Setup?

[Lostguy]

Hello!

Any code out there to loop through all the sheets in a workbook and
give a report (MsgBox, etc.) of how each sheet is setup (values of
left/right/top/bottom margins, top/bottom headers, center on page hor/
vert, adjust to x% or fit to x page by x page)?

My workbooks have many tabs and going through each and verifying this
is taking awhile.

Any help appreciated!

VR/

Lost

 

[Gary Brown]

'/=================================================/
' Sub Purpose: list pagesetup info for all worksheets
' in current workbook
'/=================================================/
'
Public Sub PageSetupData()
Dim i As Long
Dim wks As Worksheet

Sheets.Add

On Error Resume Next

Range("A1").Select

ActiveCell.Offset(0, 0).value = "WKS Name"
ActiveCell.Offset(0, 1).value = "Print Title Rows"
ActiveCell.Offset(0, 2).value = "Print Title Columns"
ActiveCell.Offset(0, 3).value = "Print Area"
ActiveCell.Offset(0, 4).value = "Left Header"
ActiveCell.Offset(0, 5).value = "Center Header"
ActiveCell.Offset(0, 6).value = "Right Header"
ActiveCell.Offset(0, 7).value = "Left Footer"
ActiveCell.Offset(0, 8).value = "Center Footer"
ActiveCell.Offset(0, 9).value = "Right Footer"
ActiveCell.Offset(0, 10).value = "Left Margin"
ActiveCell.Offset(0, 11).value = "Right Margin"
ActiveCell.Offset(0, 12).value = "Top Margin"
ActiveCell.Offset(0, 13).value = "Bottom Margin"
ActiveCell.Offset(0, 14).value = "Head Margin"
ActiveCell.Offset(0, 15).value = "Foot Margin"
ActiveCell.Offset(0, 16).value = "Print Headings"
ActiveCell.Offset(0, 17).value = "Print Gridlines"
ActiveCell.Offset(0, 18).value = "Print Comments"
ActiveCell.Offset(0, 19).value = "Print Quality"
ActiveCell.Offset(0, 20).value = "Center Horizontally"
ActiveCell.Offset(0, 21).value = "Center Vertically"
ActiveCell.Offset(0, 22).value = "Orientation"
ActiveCell.Offset(0, 23).value = "Draft"
ActiveCell.Offset(0, 24).value = "Paper Size"
ActiveCell.Offset(0, 25).value = "First Page Number"
ActiveCell.Offset(0, 26).value = "Order"
ActiveCell.Offset(0, 27).value = "Black and White"
ActiveCell.Offset(0, 28).value = "Zoom"
ActiveCell.Offset(0, 29).value = "Print Errors"

For Each wks In Worksheets
i = i + 1
ActiveCell.Offset(i, 0).value = wks.name
With wks.PageSetup
ActiveCell.Offset(i, 1).value = .PrintTitleRows
ActiveCell.Offset(i, 2).value = .PrintTitleColumns
ActiveCell.Offset(i, 3).value = .PrintArea
ActiveCell.Offset(i, 4).value = .LeftHeader
ActiveCell.Offset(i, 5).value = .CenterHeader
ActiveCell.Offset(i, 6).value = .RightHeader
ActiveCell.Offset(i, 7).value = .LeftFooter
ActiveCell.Offset(i, 8).value = .CenterFooter
ActiveCell.Offset(i, 9).value = .RightFooter
ActiveCell.Offset(i, 10).value = .LeftMargin
ActiveCell.Offset(i, 11).value = .RightMargin
ActiveCell.Offset(i, 12).value = .TopMargin
ActiveCell.Offset(i, 13).value = .BottomMargin
ActiveCell.Offset(i, 14).value = .HeaderMargin
ActiveCell.Offset(i, 15).value = .FooterMargin
ActiveCell.Offset(i, 16).value = .PrintHeadings
ActiveCell.Offset(i, 17).value = .PrintGridlines
ActiveCell.Offset(i, 18).value = .PrintComments
ActiveCell.Offset(i, 19).value = .PrintQuality
ActiveCell.Offset(i, 20).value = .CenterHorizontally
ActiveCell.Offset(i, 21).value = .CenterVertically
ActiveCell.Offset(i, 22).value = .Orientation
ActiveCell.Offset(i, 23).value = .Draft
ActiveCell.Offset(i, 24).value = .PaperSize
ActiveCell.Offset(i, 25).value = .FirstPageNumber
ActiveCell.Offset(i, 26).value = .Order
ActiveCell.Offset(i, 27).value = .BlackAndWhite
ActiveCell.Offset(i, 28).value = .Zoom
ActiveCell.Offset(i, 29).value = .PrintErrors
End With

Next wks

'format worksheet
Range("B2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:AD").Select
Columns("A:AD").EntireColumn.AutoFit
Range("B2").Select

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: PageSetupData - Module: " & _
"Mod_PageSetup_Wkst - " & Now()
GoTo exit_Sub

End Sub
'/=================================================/
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown