Sheet adjustment before printing
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Sheet adjustment before printing
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.
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Sheet adjustment before printing
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
I recommend, doing it for one sheet with the macro recorder and that should give you an idea of the objects to adjust...
Steve
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Sheet adjustment before printing
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.
Then I eliminated the default settings (at least I think that’s what they are) and kept the changes I was looking for.
Now I'll do a little more tweaking and see what kind of trouble I can get in
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
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
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Sheet adjustment before printing
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
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
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Sheet adjustment before printing
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.
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.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Sheet adjustment before printing
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.
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.
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....
Then I tried using Case, it seems to work OK
I recorded a Marco and retrieved this portion of the code.
Code: Select all
.CenterFooter = "&14&P"
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
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
-
- Administrator
- Posts: 7215
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Sheet adjustment before printing
To my inexperienced eyes, you seem to sometimes have a space between "REI" and "7", and sometimes not.ABabeNChrist wrote: 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 ... ...
And I think you have your logic inverted - should it not be
Code: Select all
If ActiveSheet.Name = "REI7-2Part1" Then
Leif
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Sheet adjustment before printing
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
StuartR
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Sheet adjustment before printing
Thank you Leif and StuartR
As always I am very grateful for everyone’s assistance here at Eileen’s Lounge.
I’ll probably go with using case
Also what about the first part of post #31096 in regards to page numbering
As always I am very grateful for everyone’s assistance here at Eileen’s Lounge.
Good catch Leif.Leif wrote: To my inexperienced eyes, you seem to sometimes have a space between "REI" and "7", and sometimes not.
I’ll probably go with using case
Also what about the first part of post #31096 in regards to page numbering
-
- PlutoniumLounger
- Posts: 15641
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Sheet adjustment before printing
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.ABabeNChrist wrote:is there a piece of code that will set each sheet to a specified setting so that all sheets will be consistent.
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.
He who plants a seed, plants life.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sheet adjustment before printing
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 = ""
.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 = ""
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Sheet adjustment before printing
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.
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.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sheet adjustment before printing
That's OK then.ABabeNChrist wrote: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.
Best wishes,
Hans
Hans