To set up each worksheet for printing in legal size you could place
Workbook_Open code or Workbook_BeforePrint code in Thisworkbook module.

I would prefer the Workbook_BeforePrint personally but your choice.

Private Sub Workbook_Open()
'or Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
'Worksheet.Add 'if you want to add a sheet at this point
'Don't run it here if using BeforePrint
For Each ws In ActiveWorkbook.Sheets
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Do you mean you really want to add a worksheet to "any" workbook that is opened?

Or just certain workbook(s) that are opened?

Your answer will determine the response and code and where to place that code.


Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 09:29:01 -0800, Le Jurassien wrote:

Hello,

How do I change my code so that any worksheets within the workbook opened to
print in legal size? Currently, I have to run my macro on every worksheet so
this does not seem to save me time.

Also, I would like to know how can set a unique command that will
automatically run the macro on any workbook opened without me going to Tool
- macro - then run.

My intent is to have a macro that add new worksheets and set the size of
the print out to landscape legal format.
Please help!

Public Sub AddAWorbook()
Worksheets.Add

End Sub
Public Sub PageSet()

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

End With
End Sub