0

I am trying to save my worksheet as a pdf file with the use of vba. The worksheet is wider than an A4 page and contains a few shapes (side to side). I want the worksheet to fit on one A4 page, so it should be rescaled as seen on screen. I am using the following code:

Sub Print_PDF()

Dim sFilename As String

Worksheets.Add.Name = "Helpsheet"
sFilename = "G:\anything\test.pdf"
ThisWorkbook.Worksheets("Newsletter").Range("A2:D81").CopyPicture xlScreen, xlBitmap

ThisWorkbook.Sheets("Helpsheet").Activate
ThisWorkbook.Sheets("Helpsheet").Paste
ActiveSheet.PageSetup.PrintArea = ThisWorkbook.Sheets("Helpsheet").Range("A1:O86")
With ActiveSheet.PageSetup
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

ThisWorkbook.Sheets("Helpsheet").ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFilename, quality:=xlQualityStandard, _
     IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Application.DisplayAlerts = False
ThisWorkbook.Sheets("Helpsheet").Delete
Application.DisplayAlerts = True

End Sub

The code actually saves a PDF file in the expected location. However, the PDF file is 4 pages instead of the expected 1 page. So it seems that the printarea is not defined correctly. What am I doing wrong?

Roosz0rd
  • 192
  • 13

2 Answers2

3

Have you tried adjusting your PageSetup parameters as per this example?

Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .Zoom = False
    '.PrintArea = Worksheets(ReportWsName).UsedRange
    .FitToPagesWide = 1
    '.FitToPagesTall = 1
End With
Application.PrintCommunication = True

Think explicitly setting .Zoom to false is important.

Source: excel vba not exporting pagesetup to pdf correctly

mquinn
  • 454
  • 4
  • 14
0

Assuming the activesheet is also the sheet("Helpsheet") The print area need the address of the print range.

ActiveSheet.PageSetup.PrintArea = ThisWorkbook.Sheets("Helpsheet").Range("A1:O86").Address
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
  • Thank you for your comment. The activesheet is indeed the sheet("Helpsheet"), since I used ThisWorkbook.Sheets("Helpsheet").Activate. I have tried your solution, but still, the PDF file is 4 pages. – Roosz0rd Dec 18 '17 at 13:43