As written, they weren't the same.

OP originally posted the PageSet and wanted to know how to do all sheets at once
and automatically without having to click a button when he opened the workbook.

I posted the Workbook_Open code with the error you picked out.

Now he has to make a choice on how to run the code.


Gord Dibben MS Excel MVP

On Thu, 04 Jan 2007 17:23:34 -0600, Dave Peterson
wrote:

I thought that the procedures were used to do different things--I didn't think
each was supposed to run against all the worksheets.

Thanks for the other interpretation <vbg.

Gord Dibben wrote:

Thanks Dave

My bad again, although I didn't post the Public Sub PageSet()

It still needs some modiification to run on all sheets.

Public Sub PageSet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Private Sub Worbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Better???

Gord

On Thu, 04 Jan 2007 12:42:24 -0600, Dave Peterson
wrote:

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

Le Jurassien wrote:

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

Private Sub Worbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Thanks,