Setting Print Size for varying Printer use

[indigo]

I have a workbook with about 20 tabs and would like them all to prin
out one page tall, one page wide on 11 x 17 paper. I can highlight al
tabs and change the setting to 11 x 17, but if I access the file o
another computer with a different printer, it resets th
configuration.

Is there a way to push a command button that changes the print size t
11 x 17 and prints the page on the default printer? Need help with th
code

 

[Tom Ogilvy]

You can get the code by turning on the macro recorder and making the change
to one sheet.

this will record all settings, even though you only change one. You don't
need to do all settings.

Select the subset you need and put this in the BeforePrint Event of the
workbook

http://www.cpearson.com/excel/events.htm

 

[Gjones]

Dear Indigo;

Use the following macro that will run automatically when
they open the work book. You will need to modify it to
run for each page.

Sub Auto_Open()

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub


Thanks,

Greg

 

[Tom Ogilvy]

This will do one page, and take a long time. It does represent what you get
when you record a macro, but not sure how
.PaperSize = xlPaperLetter
would set it to 11 x 17

This part does do the fitting:

.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1

all three lines are needed.

So as I said, you need to trim out command that are not needed such as the
headers and margins.

 

[indigo]

Ok, I think I understand. Let me give it a shot.


Thank you both

 

[sowetoddid]

I posted this under "ThisWorkbook" and it to test it changed the paper
size to legal. When I printed the document, it was not reset to 11 x
17.

I did something wrong. !

 

[Tom Ogilvy]

I will assume you didn't do what I said. You didn't record a macro where
you actually selected 11 x 17 as the paper size, then used the recorded
value in your macro.