Setting Print Area (Fit to Page) when saving ranges to PDF via macro...Please Help.

[rcicconetti]

I have a scheduling program that runs in Excel. One macro saves 7 ranges to a PDF file. The ranges, however go beyond the print limits and thus render 14 pages of PDF instead of the intended 7 pages (referring to 7 days in the week). Can someone help me to have each range "Fit to Page,"
so that when I print the PDF I only get 7 neat pages.

Sub savetopdf()
'
' savetopdf Macro
'
   Dim saveFileName As String
    Dim PDFranges As Range
    
    With ActiveSheet
        saveFileName = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & .Range("T3").Value & ".pdf"
    
        Set PDFranges = .Range("X3:AC52,AE3:AJ52,AL3:AQ52,AS3:AX52,AA59:AF111,AH59:AM111,AO59:AT111")
    End With
    
    PDFranges.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'
End Sub

 

[John_w]

Try setting the page margins to the minimum to make each range fit on 1 page: Page Layout -> Margins. Record a macro whilst you do this and you can call the macro from your code to repeat the page setup every time you create the PDF.

 

[rcicconetti]

Seems to be working...thanks!