Problem with vba code for setting print area parameters.

[exceladdict]

I have large excel 97 spreadsheet (Many formulas, Much formatting) that I have duplicated the Main Worksheet to about 25 other worksheets in the same workbook. (This gets utilized by about 6 other users via a shortcut to a template on the network). When I made the 25 identical copies it did not copy the print formating - i.e. Print Area, Fit to Print, Margins. Each worksheet only needs to print one page, but they all need to be identical.

What is wrong with the following code?
Is there an easier way to do this?
I selected the first duplicate worksheet and recorded this macro, thinking I can then just go to each sheet and run the macro. But, it takes very long to run such a simple macro (why?) even after taking out all of the unnecessary stuff I don't need to change. Then it crashes excel after the 3rd or 4th time I run it in this workbook?

Sub printsetup1()
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.SmallScroll Down:=24
Range("B1:T85&quot.Select
Range("T85&quot.Activate
ActiveSheet.PageSetup.PrintArea = "$B$1:$T$85"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$B$1:$T$85"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("B2&quot.Select
End Sub

I am new to this. Where am I going astray?

Thanks for you help.
Jim

P.S. I only need to set print area, have it fit to 1 x 1 pages, and set the margins, nothing else.

 

[SkipVought]

Hi,

Tyr this pared down model...

Sub printsetup1()
    ActiveSheet.PageSetup.PrintArea = "$B$1:$T$85"
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
End Sub

hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

just a little better...

Sub printsetup1()
    With ActiveSheet.PageSetup
        .PrintArea = "$B$1:$T$85"
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[exceladdict]

Thanks Skip,

I added the code to adjust the margins as well and it works fine - it takes only about 4 seconds, which is what I expected.

The recorded code was taking over a minute (even after I took out all the unnecessary stuff) and the main difference I see between your code and the recorded code is the Set PrintArea code is outside the With/EndWith section. Would this really have been what slowed it down and crashed excel?

Also, does this operation put stuff in temporary memory and not take it out? Because I was still getting an "Out of memory, close some applications" message (which I never get) after running your code on all of the worksheets.

Lastly (and thanks for being patient) how can the code be adjusted to do multiple sheets instead of just the active sheet?

Again, Thanks so much for your help,
Jim

 

[SkipVought]

Jim,
I can answer your last question right off...

Dim ws As Worksheet
For Each ws In Worksheets
    With ws.PageSetup
        .PrintArea = "$B$1:$T$85"
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next

Does every sheet have the same print area? Probably not, so if you set the Print Area on each sheet, then the .PrintArea assignment becomes...

      .PrintArea = [Print_Area].Address

Actually, you may not have to set the Print Area at all. You are using Fit 1 x 1 and my only question is, will there be extraneous cells on any sheet OUTSIDE of the intended print area?

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com