Save as PDF; fit sheet on one page

[Gaellus]

Hello,

I have this code which works:

Sub printPDFSave()
Dim rng As Range
Dim fPathFile As String

    Set rng = [PDFRange]
    fPathFile = [pdfPathFile]
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile, quality:=xlQualityStandard, _
        includedocproperties:=True, ignoreprintareas:=True, openafterpublish:=True
    
    
End Sub

the range and pathfile I have defined as names in the name manager.

The only problem now is that the range is too big to fit on one page so when I use the code I get a saved file which consists of 4 pages.
How can I add the option "Fit sheet on one page"?

[Arkadi]

You'd probably need to set the print area to your range, then set the .pagesetup.fitopageswide property, then when saving the pdf save the sheet not the range, but set ignoreprintareas to false.

[Gaellus]

Unfortunately I am not that good in writing code myself, so I dont exactly understand what you mean.

I found this code which maybe helps. Just dont know how to merge it into the code I already have.

With xlWBATWorksheet.ActiveSheet 
    .VPageBreaks.Add After:=Range("X1") 'where X is the last column desired
     
    With .PageSetUp 
        .FitToPagesWide = 1 
    End With 
End With

[bevc]

I think I'm being daft here, but is this not possible to do without code? Just set the print layout of the document to fit on a single page. Or record a macro of setting the print area to do so, and then look at the code.

[Gaellus]

For some reason setting a print area does not work. I still get multiple pages in my pdf. And it is a table so it is useless then.

[Arkadi]

See if this works for you (changes/additions in red):

Sub printPDFSave()
Dim rng As Range
Dim fPathFile As String
Dim ws As Worksheet

Set ws = Worksheets("Sheet1") 'change as needed
Set rng = [PDFRange]
With ws.PageSetup
    .PrintArea = rng.Address
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
End With
fPathFile = [pdfPathFile]
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile, quality:=xlQualityStandard, _
    includedocproperties:=True, ignoreprintareas:=False, openafterpublish:=True
    
    
End Sub

[Gaellus]

It does not save the file to the pathfile but only publishes it. But again in multiple pages..

EDIT: did something wrong the first time but that code works. Thanks Arkadi!

[Arkadi]

Gaellus, I'm glad it worked out

Thanks for the rep, and marking the thread as solved!