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?

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?

"Homer" wrote:

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?

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

"JLGWhiz" wrote:

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?

"Homer" wrote:

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?

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

"Homer" wrote:

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?