page setup preferances

[Matthew Dyer]

So I'd like to be able to assign page setup preferances when a macro
of mine creates new sheets. This is the code i've been able to use to
do what I need to do, but it takes much longer to perform this code
than I thought it would. Does anyone have any suggestions to perform
the same actions with code that runs more quickly? All I need to do is
set all margins to .5, set the pages wide to 1 and the pages tall to
3. I've tried to cut out some of the code that seems unecessary, but
then the pages wide/tall adjustments aren't made, only the margins.

With WSNew.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Orientation = xlPortrait
.Draft = False
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.PrintErrors = xlPrintErrorsDisplayed
.FitToPagesWide = 1
.FitToPagesTall = 3
End With

 

[Incidental]

Hi Matthew

As most of the values you don't need seem to be default values i tried
it by removing all but those related to the margins and fit to pages
values, i'm not sure why using only these values didn't work for you.
I also stopped excel from updating the screen while it makes the
changes which speeds the process up and looks cleaner. To test the
code copy the code to the workbook module

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Application.ScreenUpdating = False
With Sh.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.FitToPagesWide = 1
.FitToPagesTall = 3
End With
Application.ScreenUpdating = True

End Sub

I hope this helps you out

Steve

 

[Matthew Dyer]

I will try your modifications. But, when I did the same thing and only
kept the margin and tall/wide setups, the macro would only edit the
margins but the tall/wide fixes wouldnt work. I was thinking there's
something else in that set of 'default' setup code that was necessary
for the tall/wide to initiate. My other option is to go line by line
and keep removing pieces that can remain as default until I have only
the precise setups that I need to initiate the tall/wide part. I'll
post my findings here once I get a chance to hack it out.