how to save a sheet as a pdf using the print area in vba?

[merlin777]

I've managed to save an active worksheet as a pdf using the following:

Sub save_as_pdf()
Dim Path As String
Dim filename As String
Path = Range("B35")
filename = Range("B36")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & filename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

This code is currently on a sheet called 'Data' where its being run from a button.

How can I save a specific worksheet (it will be Sheet1,) as a pdf using the set print area for that sheet? I tried putting the code and the macro run button on sheet1 but I get a system error when i run it.

 

[merlin777]

i fixed the error and although i'm not referring to the print area in vba it seems to be defaulting to it so all sorted now.

 

[lascough]

i fixed the error and although i'm not referring to the print area in vba it seems to be defaulting to it so all sorted now.

I have borrowed your code as this is exactly what I needed. However, how do I modify this to save the pdf in a specific folder on my C: drive?

 

[merlin777]

The path is in cell B35 so just change that cell to your preferred folder path. I did that because the folder can be seen and changed without fiddling with code.
Fir instnce, my cell B35 contains C:\Users\john\Documents\

 

[lascough]

The path is in cell B35 so just change that cell to your preferred folder path. I did that because the folder can be seen and changed without fiddling with code.
Fir instnce, my cell B35 contains C:\Users\john\Documents\

Ah brilliant, that works. Thanks for that, it aint easy being a novice.

 

[merlin777]

been there, mate. It's taken a long time to go from asking for advice all the time to occasionally being able to offer it!

 

[merlin777]

If you want to change your filename every day you save it so you keep earlier versions, you can put this in B36:

="mydocname_"&TEXT(TODAY(),"ddd mmm yyyy")

TODAY() returns today's date and TEXT(value, text format) forces it into the format you want in your filename. Then whenever you save that day it'll always save with today's date and not overwrite previous days' files e.g. "mydocname_Fri Dec 2019.pdf"


.