Page setup error after moving sheet tabs



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
End Sub

This line is highlighted .PaperSize = xlPaperTabloid

Any ideas why moving the tabs is creating the error?




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?


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,





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