MACRO HELP

[cfree36]

I have the following code however it does not work.

What I want the code to do is go through each worksheet in the file and if cell A2 is >5 then set the Print Area and Page Settings as below.

If I run the code on one sheet without the for each loop the setting work... when I run this code nothing happens.

Any thoughts?

Sub SetPage()
Dim ws As Worksheet
    
For Each ws In ActiveWorkbook.Worksheets

Select Case ws.Range("A2")
Case Is > 5
    ActiveWorkbook.Names.Add Name:="print_area", RefersToR1C1:= _
        "=OFFSET(R8C2,0,0,R9C1,R5C1)"
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$10:$12"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(1.15)
        .BottomMargin = Application.InchesToPoints(1.2)
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
End Select
Next ws
End Sub

 

[Smitty]

Try this:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> SetPage()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
        
        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
                ws.Activate
                <SPAN style="color:#00007F">If</SPAN> ws.Range("A2").Value > 5 <SPAN style="color:#00007F">Then</SPAN>
                    ActiveWorkbook.Names.Add Name:="print_area", RefersToR1C1:= _
                        "=OFFSET(R8C2,0,0,R9C1,R5C1)"
                    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
                        .PrintTitleRows = "$10:$12"
                        .LeftMargin = Application.InchesToPoints(0.25)
                        .RightMargin = Application.InchesToPoints(0.25)
                        .TopMargin = Application.InchesToPoints(1.15)
                        .BottomMargin = Application.InchesToPoints(1.2)
                        .Orientation = xlPortrait
                        .PaperSize = xlPaperLetter
                        .FitToPagesWide = 1
                        .FitToPagesTall = <SPAN style="color:#00007F">False</SPAN>
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> ws
        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I changed the Select Case to an If statement, because with only 1 case it was kinda' unnecessary.

HTH,

Smitty