Macro that set up each worksheet to print in legal size
Ahhh. You remember a previous post!
There's the difference. Each post is brand new to me!
Gord Dibben wrote:
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,
--
Dave Peterson
|