Page setup error after moving sheet tabs


H

Homer

I have a code that applies a page setup to all sheets in a workbook. The
code will work fine. When I move the sheet tabs, then run the code, I get
errror # 1004 "Unable to set papersize property of pagesetup class". Here is
the code:

Sub Page_Setup_For_All_Sheets()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
With sht.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&22 2009 Prestress Quote Log"
.RightHeader = "&""Arial,Italic""&14&A"
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.05)
.RightMargin = Application.InchesToPoints(0.01)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperTabloid
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next sht
Sheets("All").Activate
End Sub


This line is highlighted .PaperSize = xlPaperTabloid

Any ideas why moving the tabs is creating the error?
 
Ad

Advertisements

J

JLGWhiz

It could be that the printer that VBA detects during the process does not
suppor 11 x 17 in size paper or that the 11 x 17 size paper tray is not
loaded when the macro runs. Did you look at the error help dialog?
 
H

Homer

After read your comments I did some testing and found that the error happens
it is based on a certain printer. I think if I can get excel to default back
to a different printer I shouldn't get the error. The problem is that if I
use a printer excel keeps that printer until changed.

Printer 1 & 2 are ok, printer 3 is causing the error. How do I make sure
printer 1 is always the default printer? If I use printer 3 for another
file, I want the excel print icon to keep printer 1.

Thank You,

Don
 
Ad

Advertisements

R

REO

The issue is in the printer driver. Update the driver to the latest and
hopefully the issue will go away.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top