Sheet adjustment before printing

[ABabeNChrist]

I have a workbook with about 30 worksheets; some are similar in size and layout where others may be different. I was noticing when I use this workbook to do different reports of which they can vary in size from sheet to sheet is that I have to re-adjust each of my sheets using page break before printing, and by doing so can cause my sheets to appear different in size from sheet to sheet when printing. I was wondering is there a piece of code that will set each sheet to a specified setting so that all sheets will be consistent.

[sdckapr]

You can loop through each sheet adjusting whatever you want in code. Without details it is tough to create the code you may be after...

I recommend, doing it for one sheet with the macro recorder and that should give you an idea of the objects to adjust...

Steve

[ABabeNChrist]

Hi Steve
That was a great idea
Here’s how my recorded session went,
then I made my desired margins and zoom% I was seeking.
At first it appeared like so.

Code: Select all

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.1)
        .RightMargin = Application.InchesToPoints(0.1)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 68
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
End Sub

Then I eliminated the default settings (at least I think that’s what they are) and kept the changes I was looking for.

Code: Select all

    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.1)
        .RightMargin = Application.InchesToPoints(0.1)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .CenterHorizontally = True
        .CenterVertically = False
        .Zoom = 68
    End With

Now I'll do a little more tweaking and see what kind of trouble I can get in

[sdckapr]

And if you want to do it to all the sheets, you can loop (warning aircode...)

Dim sht as variant
for each sht in Activeworkbook.sheets
With sht.PageSetup
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Zoom = 68
End With
Next

Steve

[ABabeNChrist]

Hi Steve
I’m probably going to stick with a single sheet at a time unless I figure out a better way. The code works great for adjusting the margins and size (zoom) which is great and that was part of my original problem but I was also having a problem with each sheet print area.
What I mean is each page is set up within selected print area and looks great at first and prints up great (with no changes to sheet)…..
BUT if I happen to enter a bunch of data in various location of sheet along with various pictures. I sometimes can have large gaps between data/pictures that cause more pages to print. I’d like to close up the open space between data. Now of course this does not happen on all sheets used to do reports only on a few?

I can’t seem to figure out why soma do and soma don’t….

Let’s say for instance I’m working on sheet1, before I even start it will print 2 pages and looks great.
Now as I work on my report entering all my data and pictures everything looks great in normal view (of course) then if I were to select print preview it could be up to 5 pages, but I only entered 1 page of data/pictures, it should only be 3 pages not 5.

Also the funny thing is, will maybe not that funny is, sheet1 and 2 may appear identical in all aspects but when data is added it’s a whole new ball game.

I’m able to manually make needed adjustment when so needed, but this can be so time consuming and a real pain.

[ABabeNChrist]

I wanted to add page numbering to the center footer of this existing code.
I recorded a Marco and retrieved this portion of the code.

Code: Select all

.CenterFooter = "&14&P"

I believe it’s correct and is all I needed, just want to make sure, just in case I possibly missed something since recorded macro’s do spit out a whole lot of misc.

Code: Select all

    Application.ScreenUpdating = False
    With ActiveSheet.PageSetup
        .CenterFooter = "&14&P"
        .LeftMargin = Application.InchesToPoints(0.1)
        .RightMargin = Application.InchesToPoints(0.1)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .CenterHorizontally = True
        .CenterVertically = False
        .Zoom = 66
    Application.ScreenUpdating = True
    End With

One more thing I wanted to ask.
I do not wish to have this code run when sheet REI 7-2 Part 1 or REI 7-2 Part 2 is active sheet. I tried a couple approaches
Here is what I have tried.
I first tried If then like so, didnt seem to work....

Code: Select all

    If ActiveSheet.Name <> "REI7-2Part1" Then
        MsgBox "Feature not available for REI 7-2 Part1"
        Exit Sub
    Else
        If ActiveSheet.Name <> "REI7-2Part2" Then
            MsgBox "Feature not available for REI 7-2 Part2"
            Exit Sub
        Else
            Application.ScreenUpdating = False
            With ActiveSheet.PageSetup
                .CenterFooter = "&14&P"
                .LeftMargin = Application.InchesToPoints(0.1)
                .RightMargin = Application.InchesToPoints(0.1)
                .TopMargin = Application.InchesToPoints(0.25)
                .BottomMargin = Application.InchesToPoints(0.5)
                .HeaderMargin = Application.InchesToPoints(0.3)
                .FooterMargin = Application.InchesToPoints(0.3)
                .CenterHorizontally = True
                .CenterVertically = False
                .Zoom = 66
                Application.ScreenUpdating = True
            End With
        End If
    End If
End Sub

Then I tried using Case, it seems to work OK

Code: Select all

    Select Case (ActiveSheet.Name)
    Case "REI 7-2 Part 1"
        MsgBox "Feature not available for REI 7-2 Part1"
        Exit Sub

    Case "REI 7-2 Part 2"
        MsgBox "Feature not available for REI 7-2 Part2"
        Exit Sub

    Case Else
        MsgBox ("Margin set")

        Application.ScreenUpdating = False
        With ActiveSheet.PageSetup
            .CenterFooter = "&14&P"
            .LeftMargin = Application.InchesToPoints(0.1)
            .RightMargin = Application.InchesToPoints(0.1)
            .TopMargin = Application.InchesToPoints(0.25)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .CenterHorizontally = True
            .CenterVertically = False
            .Zoom = 66
            Application.ScreenUpdating = True
        End With
    End Select
End Sub

[Leif]

One more thing I wanted to ask.
I do not wish to have this code run when sheet REI 7-2 Part 1 or REI 7-2 Part 2 is active sheet. I tried a couple approaches
Here is what I have tried.
I first tried If then like so, didnt seem to work....

Code: Select all

    If ActiveSheet.Name <> "REI7-2Part1" Then
        MsgBox "Feature not available for REI 7-2 Part1"
        Exit Sub
...
...

To my inexperienced eyes, you seem to sometimes have a space between "REI" and "7", and sometimes not.

And I think you have your logic inverted - should it not be

Code: Select all

    If ActiveSheet.Name = "REI7-2Part1" Then

?

[StuartR]

It is best practice to avoid using Exit Sub wherever possible, and it isn't needed here. You can simplify this to...

Code: Select all

Select Case (ActiveSheet.Name)
    Case "REI 7-2 Part 1",  "REI 7-2 Part 2"
        MsgBox "Feature not available for " & ActiveSheet.Name

    Case Else
        ' Code goes here

    End Select

End Sub

[ABabeNChrist]

Thank you Leif and StuartR
As always I am very grateful for everyone’s assistance here at Eileen’s Lounge.

To my inexperienced eyes, you seem to sometimes have a space between "REI" and "7", and sometimes not.

Good catch Leif.

I’ll probably go with using case

Also what about the first part of post #31096 in regards to page numbering

[ChrisGreaves]

is there a piece of code that will set each sheet to a specified setting so that all sheets will be consistent.

I have a utility ZoomP which will determine the optimum setting of Zoom in either landscapr, Portrait, or both for a sheet. You could apply that setting to all your sheets.
Zoomp's purpose is to avoid the orphaned columns and rows that we generate as the rightmost and lower-most sheets of paper on a multi-sheet printout.

[HansV]

Adding the line

.CenterFooter = "&14&P"

should be sufficient, unless you want to make sure that the sheet doesn't have a left and right footer - in that case should also add

.LeftFooter = ""
.RightFooter = ""

[ABabeNChrist]

Thank you Chris
ZoomP sounds interesting. With my type of reporting I use I may hide or unhide areas of interest, add photos throughout report and the amount of text can vary in various locations.
Which can cause large gaps between sheets?

And thank you Hans
I already have selected entries I’ve added to the left and right footer that are different with each sheet. So I thought leaving the left and right footer out would prevent unneeded changes.

[HansV]

I already have selected entries I’ve added to the left and right footer that are different with each sheet. So I thought leaving the left and right footer out would prevent unneeded changes.

That's OK then.