Changing worksheet attributes problem 1

[mscallisto]

I have a spreadsheet with 70 worksheets each of which I want to set the print area as well set it to landscape and fit to 1 page wide by 1 page high.

The following code doesn't seem to work, even when I added a save between iterations.

All help accepted!

Sub SetSheetAttributes()
  Dim WS As Worksheet
  Dim ct As Integer
  Application.ScreenUpdating = False
  For Each WS In Worksheets
    ct = ct + 1
    Application.StatusBar = "( " & WS.Name & " ) Worksheet# " & ct
    Range("A1:N20").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$20"
    Range("A1").Select
    With ActiveSheet.PageSetup
      .Orientation = xlLandscape
      .FitToPagesWide = 1
      .FitToPagesTall = 1
    End With
    If ct = 7 Then
     ActiveWorkbook.Save
    End If
  Next WS
End Sub

 

[TonyJollans]

Hi mscallisto,

You do not Select or Activate each sheet as you work through them all. So your references to ActiveSheet do not refer to the one you think you are working on. Try this ..

[blue]  For Each WS In Worksheets
    ct = ct + 1
    Application.StatusBar = "( " & WS.Name & " ) Worksheet# " & ct
    Range("A1:N20").Select
    [red]WS[/red].PageSetup.PrintArea = "$A$1:$N$20"
    [red]WS.[/red]Range("A1").Select [green]' You don't really need this [/green]
    With [red]WS[/red].PageSetup
      .Orientation = xlLandscape
      .FitToPagesWide = 1
      .FitToPagesTall = 1
    End With
    If ct = 7 Then
     ActiveWorkbook.Save
    End If
  Next WS[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[mscallisto]

Thanks Tony...that makes sense.

The printarea and orientation now work yet the fit to 1 page doesn't.

Any ideas?

 

[TonyJollans]

Hi mscallisto,

Before they take effect you need to select the right scaling option, which you do in a slightly contrary way ..[blue]

    With WS.PageSetup
      .Orientation = xlLandscape
      [red].Zoom = False[/red] 
      .FitToPagesWide = 1
      .FitToPagesTall = 1
    End With

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[mscallisto]

Ahhh I did see .zoom right after .BlackAndWhite but had no idea that I needed it!

Thanks again...all is well