VBA to export print range of all sheets in workbook to PDF

[z3115]

Hello,

I am trying to create a macro that will save my workbook as a PDF, but I am having trouble getting it to work properly. All I want to do is take the print range from each sheet and save it as one PDF, in the same location as the excel file, and same name (just .PDF instead of .XLSM)

Any help would be greatly appreciated. Thank you!

 

[Gavin T]

Does this help?

 Sub Print_PDF()
 
    Sheets.Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" _
        & Format(Date, "MMM-DD-YY") & ".pdf", IgnorePrintAreas:=False

End Sub

 

[z3115]

Does this help?

 Sub Print_PDF()
 
    Sheets.Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" _
        & Format(Date, "MMM-DD-YY") & ".pdf", IgnorePrintAreas:=False

End Sub

This gives me an error (run time error 1004: Method "select" of object "sheets" failed)

Any thoughts what would cause this? Thanks!

Edit: I tried this:



Dim Fname As String
Fname = Replace(ActiveWorkbook.Name, ".xlsx", ".PDF")

Sheets.Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"FilePathABC" & Fname, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

And it does publish a PDF successfully, but it's a weird pdf that appears to only take the active cell from each sheet (I need the whole sheet).

 

[Gavin T]

...Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _...

In your code you wrote Selection.Export.... You should write ActiveSheet.Export.... (See post #2)

 

[z3115]

In your code you wrote Selection.Export.... You should write ActiveSheet.Export.... (See post #2)

Good call, I fixed that though and it's still giving what appear to be a single random cell on each page. I have 14 tabs in excel and it's giving me a 14 page PDF, so I don't know what the problem is. I checked the print ranges and margins, etc. in the excel file and they are all normal.

Edit: I think I got ti figured it out. I changed "IncludeDocProperties" from True to False, and that appears to have fixed it. I don't know what settings would have made it look the way it did, but this appears to be working for me in the short term. Thank you for your help!

 

[Gavin T]

You're welcome.