After setting the page(s), I would like to automatically change the
fit to to each page to whatever setting will give me the best
printout. When this prints now, it prints too small. I dont want to
limit the total number of pages to only one (as the range may be 10
lines, or 1000 lines), but fully fit each page to one page .



This code works for me (searches for the data and prints it out on
several pages if I need, or one if there's not a lot of data). Just
need to print a little larger.


Sub SetPrtAr_MLS_setup()
Dim lLastRow As Long
lLastRow = Range("I65536:K65536").End(xlUp).Row
Orientation = xlLandscape
ActiveSheet.PageSetup.PrintArea = Range("a6:N" & lLastRow).Address
End Sub

The best ways is to get the width to fit correctly is to go to the Page
setup. Select 1 page wide by 1000 pages tall (more pages than you plan to
use, make very large).This will give youi the best fit. Excel is smart
enough to know that if you use 20 page it will only pring 20 pages not the
1000 you entered in the page setup.

the VBA code for this is the following

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1000
End With


"J.W. Aldridge" wrote:


After setting the page(s), I would like to automatically change the
fit to to each page to whatever setting will give me the best
printout. When this prints now, it prints too small. I dont want to
limit the total number of pages to only one (as the range may be 10
lines, or 1000 lines), but fully fit each page to one page .



This code works for me (searches for the data and prints it out on
several pages if I need, or one if there's not a lot of data). Just
need to print a little larger.


Sub SetPrtAr_MLS_setup()
Dim lLastRow As Long
lLastRow = Range("I65536:K65536").End(xlUp).Row
Orientation = xlLandscape
ActiveSheet.PageSetup.PrintArea = Range("a6:N" & lLastRow).Address
End Sub

Man....!

You are truly a life saver!

Thanx for the explanation and the code. This solves several HUGE
problems....

When I make it rich, I'm buying you TWO double cheese Krystals (or
White Castle up north)... With Cheese Sir!