print macro tabloid 11x17

[greatscottbattman]

Hello excel community,
I don't have much experience with scripting. All of my macros have been created by either recording macros and coping codes from help areas like this. The question I have is as follows:
I'm trying to setup a print macro so that it will print to tabloid(11x17).
The code I have thus far is below. The problem I face is that some printers will take the .xlpapertabloid but not the .xlpaper11x17 code and vice versa.
Using xerox 7335 PS ok with 11x17 but not tabloid
Using xerox 5638 ok with tabloid but not 11x17
Error message: Unable to set the papersize property of the pagesetup class.

Is there a way to program both in the script?
Any suggestions and improvements to the code would be greatly appreciated.



Private Sub CommandButton1_Click()


ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("YTD Cost").Select
ActiveSheet.PageSetup.PrintArea = "$3:$88"
With ActiveSheet.PageSetup
.PaperSize = xlPaperTabloid
End With
Range("$3:$88").Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A3:EJ3").Select
End Sub

 

[jasonb75]

Welcome to the board!

I don't have suitable printers to test this, so this is just an "it might work" theory. Please test carefully.

I've added an error handler to your original papersize code, so that it will use that one if it's valid, but if not it will (hopefully) change to the alternative type.

Private Sub CommandButton1_Click()
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("YTD Cost").Select
ActiveSheet.PageSetup.PrintArea = "$3:$88"
On Error GoTo Line1
With ActiveSheet.PageSetup
.PaperSize = xlPaperTabloid
End With
GoTo Line2
Line1:
With ActiveSheet.PageSetup
.PaperSize = xlPaper11x17
End With
Line2:
Range("$3:$88").Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A3:EJ3").Select
End Sub

Hope this helps.

 

[greatscottbattman]

Perfect.
Works great.
Thanks for the quick response.

 

[greatscottbattman]

Sorry to bug you again.
Now I'm trying to use the same code for mulitple print areas but failing miserably.
The range would be
ActiveSheet.PageSetup.PrintArea = "$3:$88"
ActiveSheet.PageSetup.PrintArea = "$175:$260"
ActiveSheet.PageSetup.PrintArea = "$347:$433"
Any suggestions to get this to work.
Thanks in advance.

 

[jasonb75]

Hmm, I'm not too sure on this without testing it.

you could try,

Private Sub CommandButton1_Click()
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("YTD Cost").Select
ActiveSheet.PageSetup.PrintArea = "$3:$88"
On Error GoTo Line1
With ActiveSheet.PageSetup
.PaperSize = xlPaperTabloid
End With
GoTo Line2
Line1:
With ActiveSheet.PageSetup
.PaperSize = xlPaper11x17
End With
Line2:
Range("$3:$88,$175:$260,$347:$433").Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A3:EJ3").Select
End Sub

I'm not sure that you need the line that I've changed to red font, I might be wrong but it looks as though the part in blue will override it anyway.

Hopefully this guess will work as well as the last one, although I'm not so confident that it will.

Jason

 

[greatscottbattman]

perfect.
You're da man.

 

[jasonb75]

Well at least I managed to get something right today