Looping problem - Slowing down macro? Set print area dynamic

[Mark O'Brien]

To answer part 1.

OK, usually using "Select" is a huge giveaway when you're trying to speed up macros. Selecting worksheets can take a second.

In the middle of your code you have:

ws.Select 

ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address 



With ActiveSheet.PageSetup 

I would suggest changing these lines to:

With ActiveSheet.PageSetup 

   .PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address 

I've not tested the code and I don't know if you can perform the operations that you want to do without the sheet being active, but this is worth a shot.

HTH

[Cosmos75]

Created code to fit certain worksheet to fit to 1 page wide and 1 page tall.

Here's my code. Had it exclude certain worksheets. But it takes a couple of seconds for each worksheet. Is because it's looping though the excluding certain worksheets part? How can I get it to exclude them once and for all and then set print areas for all other sheets??

Also, I want to just set the print area to print on ONE page only.
Can I just use only

.FitToPagesWide = 1
.FitToPagesTall = 1

and exclude the rest?
From .LeftHeader = ""
to .Zoom = False

THANKS!

CODE:
For Each ws In Worksheets
If ws.Name <> "Summary" Then
If ws.Name <> "Test" Then
If ws.Name <> "Test2" Then
If ws.Name <> "Test3" Then
If ws.Name <> "Test4" Then
If ws.Name <> "Test5" Then
ws.Select
ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.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

End If
End If
End If
End If
End If
End If

Next ws
End Sub

[ This Message was edited by: Cosmos75 on 2002-04-15 10:44 ]

[Cosmos75]

So, it's the ws.select that is making it slow and not this part?

For Each ws In Worksheets
If ws.Name <> "Summary" Then
If ws.Name <> "Test" Then
If ws.Name <> "Test2" Then
If ws.Name <> "Test3" Then
If ws.Name <> "Test4" Then
If ws.Name <> "Test5" Then

Is there a way to have it exclude those sheets only once instead of checking each shet to see if it's those 6 sheets to be excluded? (Or is it already doing that?)

[Jay Petrulis]

I believe that the slow part is the formatting. VBA is very slow in this, compared to Excel4 macros, or so I've read.

Try to quicken the formatting part by getting rid of the default values that you are not changing anyway.

Mark's suggestions also will help a lot. I'll address your other question after (if) I figure it out.

Bye,
Jay

[Cosmos75]

Thanks, Jay!

[Mark O'Brien]

Sorry, I gave you a bit of misinformation there, I told you to use:

With ActiveSheet.PageSetup 

   .PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address 

What you should use is:

With ws.PageSetup 

   .PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address

Those "If" statements shouldn't really be slowing you down too much. I can't really think of an easy way to only run this check once. However, one thing I would suggest is having a look at "Select Case" instead of the "If...Then" statements. e.g.:

   Dim ws As Worksheet

   

   

   For Each ws In Worksheets

     Select Case ws.Name

     

     Case "Test", "Test2", "Test3"  '....etc, complete this yourself

      'Do nothing

     Case Else

       'Your Code here

     End Select



   Next

The reason being is that "Select Case" is usually faster than several "If..Then" statements.

HTH

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-15 09:40 ]

[Cosmos75]

Mark O'Brien,

Thanks, will try that out! Am learning more and more each day!

[Cosmos75]

Mark O'Brien,

Changed your

With ws.PageSetup
.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address

to this (removed repeated .PageSetup)

With ws.PageSetup
.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address

I tried this

With ws.PageSetup
.PrintArea = Range("A1", Range("H65536").End(xlUp).Address)
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Doesn't work. Error with

.PrintArea = Range("A1", Range("H65536").End(xlUp).Address)

Haven't tried the case part yet...

[Mark O'Brien]

Oh yeah and you're going to have to qualify the ranges now. i.e. reference them fully. So in this case you want to put:

.PrintArea = ws.Range("A1", ws.Range("H65536").End(xlUp).Address) 

We'll get this sorted. I promise.

I just had a quick look a the code and I think this is the only line that should need this.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-15 10:04 ]

[Cosmos75]

Mark O'Brien,

I am so sorry for this trouble but it isn't working.

I get a error message

Run-time error '13':
Type mismatch

With ws.PageSetup
.PrintArea = ws.Range("A1", ws.Range("H65536").End(xlUp).Address)