MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Code to set paper size for any Excel document opened?

Posted by Frank Hudson on December 22, 2000 6:20 AM

I get lots of spreadsheets from outside the USA that are set up for A4 paper. It's a pain to always re-set the paper size to Letter before I print. I want to write some application level code to set the paper size to Letter anytime Excel opens a document.

Ideas for an easy way to do this?

I want a solution that is independent of the document being opened and would prefer not to be required to have a particular workbook open in order to make the code active.

I have been looking at Application Level Events and this seems to be the way to go. Anyone with experience with this -- I would appreciate your help!

Posted by JAF on December 27, 2000 3:28 AM


The simplest way to do this is to create a custom print macro which you keep in your Personal Macro Workbook. If you then create a new toolbar icon and assign that macro to it, then the code in that macro (see code below) will run each time.

Problem solved.

Sub print_on_letter()
ActiveSheet.PageSetup.PaperSize = xlPaperLetter
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Hope this helps