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

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,397
Office Version
  1. 2007
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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?
 
Upvote 0
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\
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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"


.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top