Hi all,
I'm not the best when it comes to VBA and this particular issue is beyond my understanding. It's a small quirk which makes it hard to describe when searching online or in forums so I apologize if it's already been answered elsewhere.
We use a costing spreadsheet template at work. It's a tall and wide document that looks fine when you print it landscape on 11x17, scaled to fit 1 page wide by 1 page tall. It's even legible when you print it on 8.5 x 11. By default, we set it to print on our colour copier in 11x17 and that always works.
My problem is that we sometimes export the spreadsheet to PDF and send it to the president who isn't so good with computers. He then prints it himself on 11x17. Lately though it doesn't work. When we export, Acrobat shows the page size as 11x8.5, even though "Choose paper source by PDF page size" is selected. When we print it with "Choose paper source by PDF page size" selected, the copier shows an error because it's looking for 12 5/8 x 17 11/16 paper. The only way to get it to print 11x17 is to turn off "Choose paper source by PDF page size" and then go to Page setup and change it to 11x17.
What's driving me nuts is this used to work fine. We didn't even have a VBA function to export to PDF. I would just click on File --> Save & Send --> Create PDF/XPS Document and that's it. When it stopped working I decided to make a VBA function to try to force 11x17 but it still doesn't work. Here's the code I'm using:
If I comment out Zoom, the PDF saves as 11x17, but it spreads the sheet out over more than one page. I'm sure I'm missing something really stupid and thank you in advance for the help!
I'm not the best when it comes to VBA and this particular issue is beyond my understanding. It's a small quirk which makes it hard to describe when searching online or in forums so I apologize if it's already been answered elsewhere.
We use a costing spreadsheet template at work. It's a tall and wide document that looks fine when you print it landscape on 11x17, scaled to fit 1 page wide by 1 page tall. It's even legible when you print it on 8.5 x 11. By default, we set it to print on our colour copier in 11x17 and that always works.
My problem is that we sometimes export the spreadsheet to PDF and send it to the president who isn't so good with computers. He then prints it himself on 11x17. Lately though it doesn't work. When we export, Acrobat shows the page size as 11x8.5, even though "Choose paper source by PDF page size" is selected. When we print it with "Choose paper source by PDF page size" selected, the copier shows an error because it's looking for 12 5/8 x 17 11/16 paper. The only way to get it to print 11x17 is to turn off "Choose paper source by PDF page size" and then go to Page setup and change it to 11x17.
What's driving me nuts is this used to work fine. We didn't even have a VBA function to export to PDF. I would just click on File --> Save & Send --> Create PDF/XPS Document and that's it. When it stopped working I decided to make a VBA function to try to force 11x17 but it still doesn't work. Here's the code I'm using:
Code:
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PaperSize = xlPaper11x17
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=SavePath & "\" & SaveFileName & " (COSTING)", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
From:=1, _
To:=5, _
OpenAfterPublish:=TrueIf I comment out Zoom, the PDF saves as 11x17, but it spreads the sheet out over more than one page. I'm sure I'm missing something really stupid and thank you in advance for the help!
