About Us

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.

Code:

 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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Page layout - page setup - items disabled	Bill D - Hubbard & Co	Excel Discussion (Misc queries)	6	February 2nd 10 04:34 PM
FORMAT EXCEL WORKBOOK (PAGE SETUP) ALL AT ONCE INSTEAD OF BY PAGE	fred	Excel Discussion (Misc queries)	1	August 11th 08 04:54 PM
HOW DO I SETUP A PAGE IN EXCEL TO GIVE TOTALS TO ANOTHER PAGE	Randy	Excel Worksheet Functions	1	February 8th 07 06:15 PM
Under Page Setup the page option of Ledger - for Office XP	Turbo	Excel Discussion (Misc queries)	2	September 27th 06 02:46 AM
Page Setup Not Fitting To One Page Wide	MDW	Excel Programming	3	September 15th 06 09:58 PM

Menu

About Us